cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AmritaOS
Helper I
Helper I

Power Pivot - Calculate difference and % difference

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

ItemAverage cost 2020Average cost 2021Variance% Change
A1011Average cost 2020 - Average Cost 2021(Average cost 2020/Average cost 2021)*Average cost 2020
B2019  
C3015  
D4050  
2 ACCEPTED SOLUTIONS
rfigtree
Resolver III
Resolver III

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)

View solution in original post

AmritaOS
Helper I
Helper I

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

 

View solution in original post

2 REPLIES 2
AmritaOS
Helper I
Helper I

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

 

rfigtree
Resolver III
Resolver III

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)

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors