Forum Discussion
How I calculate two average fields in pivot table
I'm trying to subtract two averages in pivot table using calculation filed option
"average 2 - average 1" but the result comes out incorrect it's not the subtraction of both averages
I changed the field format to average and still incorrect
Am I missing something?
This doesn't work the way you think it does :)) What happens is illustrated in K2:N4 below:
If, with the above Table1, you expect the diff. shown in I8:I9 AFAIK this isn't doable with a classic PivotTable but with Power Pivot assuming you run Excel >/=2013 on Windows
5 Replies
This doesn't work the way you think it does :)) What happens is illustrated in K2:N4 below:
If, with the above Table1, you expect the diff. shown in I8:I9 AFAIK this isn't doable with a classic PivotTable but with Power Pivot assuming you run Excel >/=2013 on Windows
- I didn't know about power pivot tools I thought it's just a subtraction between two values and it's done lol I appreciate your time in explaining the solution it's more clear now
- Glad we could help. To help others, please mark as solution (link at the bottom of each reply you get) either Riny_van_Eekelen or my reply - Thanks & Nice EOD
Aladdin_Ajaj That doesn't work within a regular pivot table. The result you see is merely the difference between the sum of the values of the two columns you used for the averages. Even when you set it to be an average.
With Power Pivot, however, you can write a few DAX measures for calculating averages and the variance between them.
A very simple example is included in the attached file.
- I didn't use power pivot before, I followed the steps in the excel file and it worked
Thank you for simplify the solution
