# Pivot Table Positive Numbers Only

#### gavs73

##### Board Regular
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

### 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
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
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
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:

#### gavs73

##### Board Regular
thank you both for your help, really appreciated

You are welcome

Replies
11
Views
169
Replies
1
Views
81
Replies
4
Views
94
Replies
1
Views
160
Replies
1
Views
93