Supress Zero Value rows in Pivot table

PwC
PwC Member
in Charting

Hi,

Do we have an option to suppress complete zero value rows from pivot table.

Thanks

Best Answers

  • DavidChurchman
    DavidChurchman Coach
    Answer ✓

    Assuming by row, you mean everything that will display as a row in the pivot table and not a row from your original dataset. If that assumption is correct, then the 0s will be all the values associated with what defines your rows and columns With that in mind, the BeastMode GrantSmith mentioned above would need to be a FIXED formula, since it will need to calculate across multiple rows.

    FIXED reference: https://domo-support.domo.com/s/article/4408174643607?language=en_US

    I think it would be something like this, but I never get FIXED formulae right on the first try:

    case when
    sum(case when Value Field= 0 then 1 end) fixed(by Rows Field 1, Rows Field 2, Column Field)=
    count(Value Field) fixed(by Rows Field 1, Rows Field 2, Column Field)
    then 'Filter this out'
    else 'Do not filter this out'
    end

    Please 💡/💖/👍/😊 this post if you read it and found it helpful.

    Please accept the answer if it solved your problem.

  • DavidChurchman
    DavidChurchman Coach
    Answer ✓

    Maybe you could create a filter on a BeastMode like this, assuming you don't have negatives that would make it inadvertently add up to 0:

    CASE WHEN

    SUM([All those value columns]) FIXED(BY [All those row columns]) = 0 THEN 'Exclude'

    ELSE 'Include

    END

    You could wrap all your value columns in an absolute value or square them if there's a chance they might add up to 0 and not all equal zero.

    If you're having trouble getting a pivot table to work the way you like, you could also do your pivoting in MagicETL. Then, you could create a filter tile after your pivot that filters only when every single value is 0.

    Please 💡/💖/👍/😊 this post if you read it and found it helpful.

    Please accept the answer if it solved your problem.

Answers

  • ColemenWilson
    ColemenWilson Coach

    You can filter out any value from being included in the pivot table that has a zero value like this:

    Screen Shot 2023-08-17 at 11.44.39 AM.png

    If I solved your problem, please select "yes" above

  • GrantSmith
    GrantSmith Coach

    Since you want to ignore if an entire row is all 0s there isn't a simple built-in way to do this. You could write a beast mode which would aggregate all of the different values together for each row and then filter them out as @colemenwilson mentioned above.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • PwC
    PwC Member

    I got 12 values in chart few of those are sum and rest are beast mode, On a table I am seeing 0 values for all values for few accounts, I don't want to show that row with zero values for all values .

    image.png

    I see those rows like below nulls and zero values

    image.png

    I don't want to show this row in UI, I hope you got my requirement properly.

    Thanks

  • DavidChurchman
    DavidChurchman Coach
    Answer ✓

    Assuming by row, you mean everything that will display as a row in the pivot table and not a row from your original dataset. If that assumption is correct, then the 0s will be all the values associated with what defines your rows and columns With that in mind, the BeastMode GrantSmith mentioned above would need to be a FIXED formula, since it will need to calculate across multiple rows.

    FIXED reference: https://domo-support.domo.com/s/article/4408174643607?language=en_US

    I think it would be something like this, but I never get FIXED formulae right on the first try:

    case when
    sum(case when Value Field= 0 then 1 end) fixed(by Rows Field 1, Rows Field 2, Column Field)=
    count(Value Field) fixed(by Rows Field 1, Rows Field 2, Column Field)
    then 'Filter this out'
    else 'Do not filter this out'
    end

    Please 💡/💖/👍/😊 this post if you read it and found it helpful.

    Please accept the answer if it solved your problem.

  • DavidChurchman
    DavidChurchman Coach
    Answer ✓

    Maybe you could create a filter on a BeastMode like this, assuming you don't have negatives that would make it inadvertently add up to 0:

    CASE WHEN

    SUM([All those value columns]) FIXED(BY [All those row columns]) = 0 THEN 'Exclude'

    ELSE 'Include

    END

    You could wrap all your value columns in an absolute value or square them if there's a chance they might add up to 0 and not all equal zero.

    If you're having trouble getting a pivot table to work the way you like, you could also do your pivoting in MagicETL. Then, you could create a filter tile after your pivot that filters only when every single value is 0.

    Please 💡/💖/👍/😊 this post if you read it and found it helpful.

    Please accept the answer if it solved your problem.

  • pawan1_kumar
    pawan1_kumar Member

    Above suggested solution is not expected solution OR how this works in other BI Tools (Eg. SAP BW OR AO Tool). Generally in all BI Solutions Key Figures / KPIs if have ZERO values then those rows get suppressed automatically. Please help to find this solution ASAP>

Categories