Forum Discussion

datadelta's avatar
datadelta
Icon for Helper III rankHelper III
5 years ago
Solved

Multiple columns within matrix with zero values - Need to exclude zero values without impact

I have a matrix made up of stores and then items sold.  How can I remove any of the columns (stores) that have zero value without impacting the other columns in the matrix.  I only need to see store ...
Need Help
Tips and Tricks
  • goncalogeraldes's avatar
    goncalogeraldes
    5 years ago

    Hello there datadelta ! Try unpivoting your columns, like so:

     

     

    Select your "Store" column and the "Unpivot other columns" option in order to have only three columns: one with the name of the store, another with the product sold and the last with the amount. With this you can freely apply the formula above!

     

    Hope this answer solves your problem! If you need any additional help please tag me in your reply.
    If my reply provided you with a solution, pleased mark it as a solution ✔️ or give it a kudoe 👍
    Thanks!

    Best regards,
    Gonçalo Geraldes

goncalogeraldes's avatar
goncalogeraldes
Icon for Super User rankSuper User
5 years ago

Hello there datadelta ! If you a ranking formula that allows you to drill-down in the data while having it dynamically change you can try this:

 

Rank = 
IF (
    HASONEVALUE ( 'Table'[Sub-Type] ),
    RANKX (
        FILTER ( ALL ( 'Table' ), 'Table'[Category] = MAX ( 'Table'[Category] ) ),
        CALCULATE ( SUM ( 'Table'[Value] ) ),
        ,
        DESC
    ),
    RANKX (
        ALL ( 'Table' ),
        CALCULATE ( SUM ( 'Table'[Value] ), ALLEXCEPT ( 'Table', 'Table'[Category] ) ),
        ,
        DESC,
        DENSE
    )
)

 

 

Hope this answer solves your problem! If you need any additional help please tag me in your reply.
If my reply provided you with a solution, pleased mark it as a solution ✔️ or give it a kudoe 👍
Thanks!

Best regards,
Gonçalo Geraldes

  • datadelta's avatar
    datadelta
    Icon for Helper III rankHelper III
    5 years ago

    Here is my sample that I'm struggling to put into your solution.

     

    Thank you for your help!

     

    Store Sweater  Umbrella  Phone Stand  Prints  Clocks  Action Item  USB Extender  Organizer 
    West Street $             -   $               -   $                      -   $               -   $           -   $                    -   $           1,250.00 $                   150.00
    Jon's Goods $             -   $               -   $                      -   $               -   $           -   $          500.00 $                        -   $                            -  
    Tic Tac - Knic Knac $             -   $               -   $               35.00 $               -   $           -   $                    -   $                        -   $                   650.00
    Grandmas Closet $   850.00 $               -   $                      -   $  1,200.00 $           -   $                    -   $                        -   $                            -  
    The Applecart $             -   $               -   $                      -   $               -   $  302.00 $       3,500.00 $              650.00 $               1,865.00
    • goncalogeraldes's avatar
      goncalogeraldes
      Icon for Super User rankSuper User
      5 years ago

      Hello there datadelta ! Try unpivoting your columns, like so:

       

       

      Select your "Store" column and the "Unpivot other columns" option in order to have only three columns: one with the name of the store, another with the product sold and the last with the amount. With this you can freely apply the formula above!

       

      Hope this answer solves your problem! If you need any additional help please tag me in your reply.
      If my reply provided you with a solution, pleased mark it as a solution ✔️ or give it a kudoe 👍
      Thanks!

      Best regards,
      Gonçalo Geraldes