Paul has attended:
Excel Advanced - Formulas & Functions course
Power BI Reporting course
Customer Service Excellence course
Power Query / Pivot Table Calculated Field
Hi,
Difficult to explain, but will try! (formatting slightly moved)
Category Qnty Value Average
AB 304 595 1.957
AB 256 595 2.324
AB 572 1000 1.748
AC 556 999 1.797
AC 576 999 1.734
ED 640 644.1 1.006
ED 640 999 1.561
Total 3544 5831.1 1.645
Taking "AB" as example. The average for this is 1.93 (2190 / 1132) Putting this into a pivot will show average of 2.010. I believe its due to "average of average". I require the Pivot to display the 1.93
I`m pulling data via a power query, so cannot add calculated field in the pivot. I`m looking for the code to add into the power query? (I think). Thanks fir any input
RE: Power Query / Pivot Table Calculated Field
Hi Paul,
Thank you for the forum question.
I have attached an Excel file, where you can find a solution.
The data have to be grouped in the query, and a custom column need to be added. In the custom column you have to do the diversion.
I hope this makes sense.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
group data pq.xlsx
