Do not display zero value rows in Pivot table

[Priyanka]

Hi,
I have two 'value' columns in pivot table that have both zero and non-zero values in them. But there are also some rows with zero values in both the columns. I cant really use the column1>0 filter, this would eliminate rows where there could be non-zero value in column2. I just want to eliminate rows where both the columns have zero values. 

Suggestions please.

[HamzaJ · Accepted solution]

Hi @Priyanka ,

You could use a formula that checks these values and filter on that in the widget.

Something like:

• Column 1
• Column 2
• Column 3

In column 3 add the following formula:

Case
when SUM([Column 1]) = 0 and SUM([Column 2]) = 0 then 0
else 1
end

This will create a column with only 0s en 1s. 0 means both column 1 and 2 are 0. 1 means one of or both columns contain a value bigger then 0. 

After adding this formula, hover over the field and click on the filter-icon. Select only the value 1 and press OK. Your table is now filtered the way you like it. You can disable the column 3 with the toggle

Hope this helps

Hamza