10 Replies Latest reply on May 25, 2017 9:44 AM by Okechukwu Ossai

    Measure Values as % of One Particular Measure Value

    Emily Hawks

      I've created a toy example in the attached workbook.  I understand why it's not working, but I'm not sure the best way to solve the problem.

       

      In my example, I have sales data over 3 days by region.  I have regions A, B, C, and D.  What I want to show is the sales of regions A, B, and C as a percentage of the sales in region D, by day.

       

      What I initially tried to do was to create a measure called "ABC_Sales" with an if statement (IF Region <> "D" then Sales else 0 END).  This would be my numerator.

      Then I created my denominator metric "D_Sales" with an if statement (IF Region = "D" then Sales else 0 END).

      Finally to show my percentage, I took SUM(ABC_Sales)/Sum(D_Sales).

      However when I try to view this by day, and add "Region" the the color panel, all values immediately go to zero.  This makes sense as the universe cannot simultaneously include ABC and D.  But, how do I solve it?

       

      Thanks for any help you can provide.