cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
tmore Regular Visitor
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

Accepted Solutions
Community Support Team
Community Support Team

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

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 Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
5 REPLIES 5
Community Support Team
Community Support Team

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

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 Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
tmore Regular Visitor
Regular Visitor

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

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

Community Support Team
Community Support Team

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

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 Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
tmore Regular Visitor
Regular Visitor

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

@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

 

Highlighted
Community Support Team
Community Support Team

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

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 Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |