Forum Discussion

Priyanka's avatar
Priyanka
Data Storage
08-04-2022
Solved

Do not display zero value rows in Pivot table

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.

Dashboards & Reporting
  • HamzaJ's avatar
    HamzaJ
    08-05-2022

    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

     

     

1 Reply

Replies have been turned off for this discussion
  • HamzaJ's avatar
    HamzaJ
    Data Integration
    08-05-2022

    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