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

[tmore]

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.

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

Regards,

Tushar

[Anonymous]

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

[Anonymous]

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))

[tmore]

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

[Anonymous · 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] ) 
    )

Regards,

Xiaoxin Sheng

[tmore]

@Anonymous 

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.

Any thoughts how to achieve this?

Regards,

Tushar

[Anonymous]

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