Sum of Average Values in Pivot Table

simonstone

New Member
Joined
Feb 2, 2018
Messages
6
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

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
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
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,913
Members
448,532
Latest member
9Kimo3

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?

Disable AdBlock

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

Disable AdBlock Plus

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
Back
Top