Pivot Table Positive Numbers Only

[gavs73]

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

 

[mrshl9898]

You could add a column with P or N as a result of a formula to see if they are positive or negative.

 

[sandy666]

with Power Query and with Pivot Table as result

Product Price Attribute Greater item1 -3 item2 -9 Row Labels Sum of Value item3 -1 item4 4 item4 4 item5 6 item5 6 item7 8 item6 -9 Grand Total 18 item7 8 item8 -10

Attribute Less Row Labels Sum of Value item1 -3 item2 -9 item3 -1 item6 -9 item8 -10 Grand Total -32

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

 

[gavs73]

thank you both for your help, really appreciated

 

[sandy666]

You are welcome