Sum only unique values in pivot

dianamruelas

New Member
Joined
Sep 28, 2015
Messages
44
Office Version
  1. 365
Platform
  1. Windows
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:
invoiceitemchargebalance
p100a
123​
50​
p100a
124​
50​
p100a
125​
50​
p300b
126​
25​
p400b
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 LabelsSum of chargeSum of UNIQUE balance
p100
372​
50
p300
126​
25​
p400
127​
30​
Grand Total
625​
205​
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
like this?

invoiceitemChargeBalance
p100a37250
p300b12625
p400b12730
Total625105
 

dianamruelas

New Member
Joined
Sep 28, 2015
Messages
44
Office Version
  1. 365
Platform
  1. Windows
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

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
this is Power Query
so you can get the table from code
Power Query:
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

New Member
Joined
Sep 28, 2015
Messages
44
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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?
 

CA_Punit

Well-known Member
Joined
Nov 18, 2019
Messages
884
Office Version
  1. 365
Platform
  1. Windows
Do you require Formula Based Solution??
 

CA_Punit

Well-known Member
Joined
Nov 18, 2019
Messages
884
Office Version
  1. 365
Platform
  1. Windows
Try This

Book1
ABCD
1invoiceitemchargebalance
2p100a12350.00
3p100a12450.00
4p100a12550.00
5p300b12625.00
6p400b12730.00
7
8
9Row LabelsSum of chargeSum of UNIQUE balance
10p10037250
11p30012625
12p40012730
13Grand Total625105
Sheet4
Cell Formulas
RangeFormula
B10:B12B10=SUMIF($A$2:$A$6,A10,$C$2:$C$6)
C10:C12C10=AVERAGEIF($A$2:$A$6,A10,$D$2:$D$6)
B13:C13B13=SUM(B10:B12)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,464
Messages
5,596,288
Members
414,052
Latest member
Dual Showman

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
Top