how to create measure formula in power pivot . i want to calculate average based on value in column show in current pivot table
| Score | ||||
| COMMUNICATIVE | 76% | |||
| EMPATHETIC | 97% | |||
| I MANAGE EXPECTATIONS Total | 85% | 86% | Average in column value | |
| ACCOUNTABLE | 93% | |||
| FOLLOW THROUGH | 41% | |||
| PROACTIVE | 29% | |||
| I OWN IT Total | 51% | 54% | ||
| CONSULTATIVE | 79% | |||
| RELIABLE | 85% | |||
| I’M AN EXPERT Total | 82% | 82% | ||
| Grand Total | 78% | |||
Hi
are you able to upload a sample of your worksheets ? are these sectors tagged with a group name ? if yes it will be easier to use Group By in Power Query ( you mentioned power pivot)
or you just want to use power pivot to cleanse and transform into the three average values, what is the Grand Total? how you got 78%
I would be careful of the average of the percentages concept. Unless you are absolutely sure that the percentages are always calculated based on the same denominator. Different denominators could skew your analysis.
760/1000 = 76%
29/30 = 97%
51/60 = 85%
The combined weighted average info results in 840/1090 which is 77% not the 86% that the above shows ((76%+97%+85%) /3).
Just some food for thought.
