Sum of Average Values in Pivot Table

simonstone

New Member
Hi all,

I have a Pivot Table that has to have values processed as averages. This is because the data export I am using lists the total value of an order for each line. For example, an order ID might be spready across 5 lines of data, with the total order amount of, say, \$500 in each line. The total order was \$500, not \$2500. This can be resolved by producing a pivot table with Average values.

However, it is possible to produce a subtotal Sum of those averages?

Every time I change to "Sum" I lose the average and said order ID would be summed at \$2500, not the correct \$500.

Thanks in advance if anyone has any thoughts.

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Possible in a Power Pivot where you can write DAX, not so in a standard one, where you need to use one of the given aggregates. You can't alter that with any logic.
Excel Formula:
``Total:=calculate(sum(Range[amount]),filter(Range,[line]=1))``
or alternative using Power Query
Power Query:
``````// Table1
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"order", type text}, {"line", Int64.Type}, {"amount", Int64.Type}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"line"}),
#"Removed Duplicates" = Table.Distinct(#"Removed Columns")
in
#"Removed Duplicates"``````
Book1
ABCDEFGHIJKL
1Power PivotPower Query loaded to Pivot Table
2orderlineamountRow LabelsTotalRow LabelsSum of amount
3a1500a500a500
4a2500b250b250
5a3500c300c300
6b1250Grand Total1050Grand Total1050
7b2250
8b3250
9b4250
10c1300
11c2300
12
Sheet1

Replies
0
Views
665
Replies
5
Views
397
Replies
3
Views
564
Replies
5
Views
77
Replies
1
Views
188

1,202,991
Messages
6,052,965
Members
444,622
Latest member
Kriszilla

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back