Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
tmore
Regular Visitor

Lookupvalue error: A table of multiple values was supplied where a single value was expected.

Hi Folks,

 

I normally use Tableau and have recently started using Power BI, so don't know how to implement the desired functionality. I want to find percent change relative to the first year. For example, in my data source the first year is 1947 so I want to find percent change of each year wrto this year. In Tableau, I can do this with few clicks but as I am new to Power BI so struggling a bit. I created a formula but it is not working.

Capture.PNG

 

Please someone help me. If required, I am ready to send my .pbix file.

 

Regards,

Tushar

1 ACCEPTED SOLUTION

HI @tmore,

 

I can't find the specific column which you used in your formula.
If you means get the result of 'each year' divide 'all year'(per ethnicity), you can refer to below formula.

 

Measure formulas:

Spoiler
War percent = 
CALCULATE ( SUM ( Sheet2[% of WAR] ),  VALUES ( Sheet2[Ethnicity] ) ,VALUES(Sheet2[Year]))
    / CALCULATE (
        SUM ( Sheet2[% of WAR] ),
        ALL ( Sheet2 ),
        VALUES ( Sheet2[Ethnicity] )
    )

Players percent = 
CALCULATE ( SUM ( Sheet2[% of Players] ), VALUES ( Sheet2[Ethnicity] ) ,VALUES(Sheet2[Year]))
    / CALCULATE (
        SUM ( Sheet2[% of Players] ),
        ALL ( Sheet2 ),
        VALUES ( Sheet2[Ethnicity] ) 
    )

13.PNG

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

6 REPLIES 6
v-shex-msft
Community Support
Community Support

HI @tmore,

 

It seems like lookupvalue function can find out multiple correspond records, current power bi not support to direct calculation on multiple records.

 

Please share some sample data or pbix file to help us modify on your formula.

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Anonymous
Not applicable

Hi

 

I am facing the similar error in the expression

 

Could you please help me

 

LOOKUPVALUE(psc_historical[ADM.stage],psc_historical[PBM.pincode],psc_historical[PBM.pincode],psc_historical[scorecard_month_no],if(month(psc_historical[scorecard_date]) = 1,12,month(psc_historical[scorecard_date])-1))

Hi Xiaoxin,

 

Thanks for the prompt reply. I don't how to attach file in the thread. However, I've uploaded the excel file on my dropbox and sharing link with you so that you can download.

https://www.dropbox.com/s/ich5eopzldpmq50/MLB%20Ethnicity%201947-2016.xlsx?dl=0

 

Please show me the way to achive the desired functionality.

 

Let me know if you need more info regarding my problem.

 

Regards,

Tushar

HI @tmore,

 

I can't find the specific column which you used in your formula.
If you means get the result of 'each year' divide 'all year'(per ethnicity), you can refer to below formula.

 

Measure formulas:

Spoiler
War percent = 
CALCULATE ( SUM ( Sheet2[% of WAR] ),  VALUES ( Sheet2[Ethnicity] ) ,VALUES(Sheet2[Year]))
    / CALCULATE (
        SUM ( Sheet2[% of WAR] ),
        ALL ( Sheet2 ),
        VALUES ( Sheet2[Ethnicity] )
    )

Players percent = 
CALCULATE ( SUM ( Sheet2[% of Players] ), VALUES ( Sheet2[Ethnicity] ) ,VALUES(Sheet2[Year]))
    / CALCULATE (
        SUM ( Sheet2[% of Players] ),
        ALL ( Sheet2 ),
        VALUES ( Sheet2[Ethnicity] ) 
    )

13.PNG

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

@v-shex-msft 

 

Thanks for the answer. I think we are very close. Actually, with the data set I want to find percent change of each year from the starting year (1947) for each ethnicity. Here is an image what I am looking for. I made this using Tableau and want to replicate using Power BI.

 

tab.png

 

Any thoughts how to achieve this?

 

Regards,

Tushar

 

HI @tmore,

 

You can create line chart with year to axis, ethnicity to legend, measure to value field to create a similar graph.

 

Notice: current line chart not support use multiple value and legend at same time, if you enable legend field, value field can only use one column/measure.

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.