Power Query / Pivot Table Calculated Field | Excel forum

Resolved · Low Priority · Version 365

Wed 11 May 2022, 13:49 Delegate Paul said...

Paul has attended:
Excel Advanced - Formulas & Functions 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

Thu 12 May 2022, 10:19 Trainer Jens said...

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

Attached files...

group data pq.xlsx

Thu 19 May 2022: Automatically marked as resolved.

Training courses

	98.7 % recommend us
	More live stats...

Training information:

Excel tip:

Selecting blank cells within a range of data

Select the range of data which includes the blank cells that you would like to select. Press the F5 key, this will take you to the GoTo dialogue box where you can click on Special and then select Blanks.

View all Excel hints and tips