Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to Solution.
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)
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])
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])
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)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
41 | |
19 | |
19 | |
16 | |
15 |
User | Count |
---|---|
49 | |
26 | |
22 | |
17 | |
16 |