Forum Discussion

sridharpolina's avatar
sridharpolina
Helper I
2 years ago

collapse columns when column totals are zero

I'm trying to write a measure where the columns with column totals as zero would be collapsed when the measure is selected. One view should show all the columns but if the measure is selected then the matrix visual should show only the columns which have values in the row totals. As an example the below picture should show all 4 rows by default but should show only one column which has a column total. Any help would be much appreciated.

 

 

Need Help
Show and Tell
Tips and Tricks

2 Replies

  • rajendraongole1's avatar
    rajendraongole1
    Super User
    2 years ago

    Hi sridharpolina - Can you try the below approach to hide the zeros and show values

     

    create one measure for 

    Non-ZeroRowTotalFlag =
    IF(
    [RowTotal] <> 0,
    1,
    0
    )

     

    after that you can create a dynamic measure to pass it matrix visualization

    Main =
    IF(
    [Non-ZeroRowTotalFlag] = 1,
    SUM(Sales[Amount]),
    BLANK()
    )

    also you can apply conditional formatting to set the background color to white (or your matrix background color) for zero values.

     

    Try above approach and let know

     

    Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! 

    • sridharpolina's avatar
      sridharpolina
      Helper I
      2 years ago

      Thanks for replying back for my query. I am already hiding the zeros and blank from the columns but need to hide/collapse via DAX or bookmarks where I can show only a set of columns where the row total is not zero. If the default view has 20 columns then the hidden/collapsed view would have only 12 if the column totals are zero