Sum of Average Values in Pivot Table

[simonstone]

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.

 

[GraH]

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.

Total:=calculate(sum(Range[amount]),filter(Range,[line]=1))

or alternative using 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
	A	B	C	D	E	F	G	H	I	J	K	L
1							Power Pivot			Power Query loaded to Pivot Table
2		order	line	amount			Row Labels	Total		Row Labels	Sum of amount
3		a	1	500			a	500		a	500
4		a	2	500			b	250		b	250
5		a	3	500			c	300		c	300
6		b	1	250			Grand Total	1050		Grand Total	1050
7		b	2	250
8		b	3	250
9		b	4	250
10		c	1	300
11		c	2	300
12
Sheet1