topic Re: Power Pivot - Calculate difference and % difference in DAX Commands and Tips

Power Pivot - Calculate difference and % difference

AmritaOS — Fri, 07 May 2021 13:39:37 GMT

Hi All, i have the below pivot table and want to calculate the difference in avg cost from 2020 to 2021 and the % difference of teh same. in the piviot table source the year is a single column named year and items appear in multiple rows and i have just created a measure in DAX to calculate the average cost and then in the pivot table added year to the columns.

thanks!!

Item	Average cost 2020	Average cost 2021	Variance	% Change
A	10	11	Average cost 2020 - Average Cost 2021	*(Average cost 2020/Average cost 2021)Average cost 2020**
B	20	19
C	30	15
D	40	50

Re: Power Pivot - Calculate difference and % difference

rfigtree — Sat, 08 May 2021 00:15:08 GMT

Could get all fancy but if simple one off. Something like this.

Avg2020:=calculate(....,filter(all(table,date),year=2020)

Same for 2021

Difference measure just subtract the two measures. Percent difference measure is divide(difference measure,2020measure)

Re: Power Pivot - Calculate difference and % difference

AmritaOS — Thu, 13 May 2021 11:39:37 GMT

Hi All

this is what i did eventually:

CALCULATE([Average cost, TableName(column) = "2020"])

CALCULATE([Average cost, TableName(column) = "2021"])

% Difference =

=IF(

OR([Avg Part Cost 2020]<=0, [Avg Part Cost Post 2021]<=0),"na",[Avg Part Cost 2021]-[Avg Part Cost 2020])