topic Evaluate measure based on column in pivot table (not related to data set) in DAX Commands and Tips

Evaluate measure based on column in pivot table (not related to data set)

Anonymous — Thu, 18 Mar 2021 13:33:02 GMT

Hi all,

Would be very appreciative if anyone could help with this issue (probably very simple but not sure).

In summary I have 3 tables of data, with 3 seperate measure I need to calculate in each (its more but scenrio hopefully enough to explain), so essentially end up with 9 measures.

I would like to display as below in pivot table:

	Actual	Prior	YTD
Country 1
Revenue	Measure 1	Measure 2	Measure 3
Costs	Measure 4	Measure 5	Measure 6
Other	Measure 7	Measure 8	Measure 9

Country 2
Revenue	Measure 1	Measure 2	Measure 3
Costs	Measure 4	Measure 5	Measure 6
Other	Measure 7	Measure 8	Measure 9

Moving the values around in the field box makes all measures either rows or columns so can't get them to show like this.

Have tried to create a new table of just: Actaul, Prior, YTD as values, and then use if statements in a new measure e.g.:

Revenue:=if(Value="Current",Measure1,if(Value="Prior",Measure2....) etc but the ifs don't work and not sure how else to structure it.

Not sure if the pivot table needs altering, or could somehow use a new date table with those values in, or if I need to change the meaures.

Any help would be very much appreciated.

Thanks,

Alex

Re: Evaluate measure based on column in pivot table (not related to data set)

amitchandak — Thu, 18 Mar 2021 14:45:11 GMT

Anonymous , You need to split the measure, You have to create a table like

example table to be used for this display

union(
summarize('Table','Table'[country],"Revenue","sales","This period",[Revenue YTD],"Last period",[Revenue LYTD],"POP",[Revenue YOY])
summarize('Table','Table'[country],"Costs","unit","This period",[Costs YTD],"Last period",[Costs LYTD],"POP",[Costs YOY])
)

Re: Evaluate measure based on column in pivot table (not related to data set)

Anonymous — Wed, 24 Mar 2021 09:22:40 GMT

Hi there,

Many thanks for your repsonse! I have tried to write this up but not clear exactly how to. Should I be writing this out as a new DAX formula? I am using PowerPivot not PowerBI.

Thanks,

Alex