Pivot Table Positive Numbers Only

gavs73

Board Regular
Joined
Apr 22, 2008
Messages
116
Hi

Is there a way to get a Pivot Table to only Sum Positive or Negative Values ?, I have a column of numbers, and I want to split it in the Pivot Table as a Positive set and a Negative Set.

Obviously I could split the column in the Excel sheet first and then sum those two columns, but I'd prefer to do it in the Pivot Table with a Custom Field if possible, as it is a large data set and it refreshes via SQL.

Thanks in advance for any help.

Kind Regards
Gavin
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,414
You could add a column with P or N as a result of a formula to see if they are positive or negative.
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
6,699
with Power Query and with Pivot Table as result
ProductPriceAttributeGreater
item1-3
item2-9Row LabelsSum of Value
item3-1item44
item44item56
item56item78
item6-9Grand Total18
item78
item8-10
AttributeLess
Row LabelsSum of Value
item1-3
item2-9
item3-1
item6-9
item8-10
Grand Total-32
ptfields.png
Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Greater = Table.AddColumn(Source, "Greater", each if [Price] > 0 then [Price] else null),
    Less = Table.AddColumn(Greater, "Less", each if [Price] < 0 then [Price] else null),
    UOSC = Table.Unpivot(Less, {"Greater", "Less"}, "Attribute", "Value")
in
    UOSC
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,108,741
Messages
5,524,563
Members
409,585
Latest member
Aftab Anwar Yousaf Zai

This Week's Hot Topics

Top