Forum Discussion
Divide rows by specific total row
I've looked through the boards and have seen solutions that came close to what I am trying but dont work exactly.
I am looking to create a % Share metric based on dividing the sales of each product, by a row title "Total Products". Total Products is not a sum of the products listed below but includes every possible product in that category, some of which we dont pull data for.
Essentially I need a measure that is [Product] / [Total Products] but both are in the same column in the same table. I have been able to use Filter to return the $ sales of "Total Products" but have not been able to replicate that value across the rest of the rows.
sshook Assuming you have a production dimension with all the products, you can write the following measures to get the % and also the sum of all products at the total:
Base Measure = SUM ( Table[Sales] ) % Share = DIVIDE ( [Base Measure], CALCULATE ( [Base Measure], ALL ( ProductTable ) ) ) $ Sales for Visual = IF ( HASONEVALUE ( ProductTable[Product] ), [Base Measure], CALCULATE ( [Base Measure], ALL ( ProductTable ) ) )
3 Replies
sshook Assuming you have a production dimension with all the products, you can write the following measures to get the % and also the sum of all products at the total:
Base Measure = SUM ( Table[Sales] ) % Share = DIVIDE ( [Base Measure], CALCULATE ( [Base Measure], ALL ( ProductTable ) ) ) $ Sales for Visual = IF ( HASONEVALUE ( ProductTable[Product] ), [Base Measure], CALCULATE ( [Base Measure], ALL ( ProductTable ) ) )
