Forum Discussion
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 with their associated items.
I've tried using the filters and the advanced filters for if/and/or but it doesn't break it down. If I try to eliminate zeros in the data view, it impacts the other columns as well.
What I'm trying to get at is a ranking of stores and who sold the most product by product.
Thanks.
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
4 Replies
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 GeraldesHere 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 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
So I'm struggling with this. If my first column is store and then I have 20 columns listing products across and then dollar amounts in the cells below aligned by store... How do I make this work. I've never used this before.
