cancel
Showing results for 
Search instead for 
Did you mean: 

Do not display zero value rows in Pivot table

Priyanka
7 - Data Storage
7 - Data Storage

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.

1 ACCEPTED SOLUTION

HamzaJ
10 - ETL
10 - ETL

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

 

 

View solution in original post

1 REPLY 1

HamzaJ
10 - ETL
10 - ETL

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