Sum only unique values in pivot

[dianamruelas]

Is there a way I can sum unique values in a pivot? I receive a report that forces a few different data sources together. I need the sum of the charge column, but the challenge is that I need only the sum of unique values in the balance column. Here's my table:

invoice	item	charge	balance
p100	a	123​	50​
p100	a	124​	50​
p100	a	125​	50​
p300	b	126​	25​
p400	b	127​	30​

For invoice p100, the balance is only $50 total and this is what I would like to display in a pivot. But it is listed multiple times. This would reflect an erroneous balance of $150 in the pivot. Here's the pivot I would like:

Row Labels	Sum of charge	Sum of UNIQUE balance
p100	372​	50​
p300	126​	25​
p400	127​	30​
Grand Total	625​	205​

 

[sandy666]

like this?

invoice	item	Charge	Balance
p100	a	372	50
p300	b	126	25
p400	b	127	30
Total		625	105

 

[dianamruelas]

Yes, that's the pivot result I want. What settings do I use with the pivot? The spread sheet I'm pivoting is actually hundreds of thousands of lines.

 

[sandy666]

this is Power Query
so you can get the table from code

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    Grp = Table.Group(Source, {"invoice", "item"}, {{"Charge", each List.Sum([charge]), type number}, {"Count", each _, type table}}),
    Balance = Table.AddColumn(Grp, "Balance", each List.Distinct([Count][balance])),
    Expand = Table.ExpandListColumn(Balance, "Balance")
in
    Expand

then add Totals Row if necessary

 

[dianamruelas]

Thanks so much for your help. But I am not familiar with power query. Is there a way to do this with a traditional pivot?

 

[sandy666]

maybe with Power Pivot

 

[CA_Punit]

Do you require Formula Based Solution??

 

[CA_Punit]

Try This

Book1
	A	B	C	D
1	invoice	item	charge	balance
2	p100	a	123	50.00
3	p100	a	124	50.00
4	p100	a	125	50.00
5	p300	b	126	25.00
6	p400	b	127	30.00
7
8
9	Row Labels	Sum of charge	Sum of UNIQUE balance
10	p100	372	50
11	p300	126	25
12	p400	127	30
13	Grand Total	625	105
Sheet4

Cell Formulas
Range	Formula
B10:B12	B10	=SUMIF($A$2:$A$6,A10,$C$2:$C$6)
C10:C12	C10	=AVERAGEIF($A$2:$A$6,A10,$D$2:$D$6)
B13:C13	B13	=SUM(B10:B12)