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

# Measure Values as % of One Particular Measure Value

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.

• ###### 1. Re: Measure Values as % of One Particular Measure Value

Hi, Emily

You can use LOD to get the number as you expected. see below snapshot What I did is just change your two calculation fields as below:

D_SALES

{FIXED [Date]:SUM(IF [Region] = "D" THEN [Sales] ELSE 0 END)}

ABC_SALES

{FIXED [Date]:SUM(IF Region <> "D" THEN Sales ELSE 0 END)}

However, when use this to the line chart, all four regions get the same line and they overlap each other

ZZ

• ###### 2. Re: Measure Values as % of One Particular Measure Value

Thanks for your reply ZZ.  I probably didn't explain that very well before, but what I was hoping was a line chart showing:

A_Sales / D_Sales

B_Sales / D_Sales

C_Sales / D_Sales

Do you know of a way to do this?

• ###### 4. Re: Measure Values as % of One Particular Measure Value

Hi Emily,

You can use this

[ABC_SALES]

IF [Region] <> "D" THEN { FIXED [Date], [Region]: SUM([Sales])} END

[D_SALES]

{FIXED [Date]: MAX(IF [Region] = "D" THEN [Sales] END)}

[% of Sales]

SUM([ABC_SALES])/SUM([D_SALES])

Hope this helps.

Ossai 1 of 1 people found this helpful
• ###### 5. Re: Measure Values as % of One Particular Measure Value

Oh I didn't refresh my browser, seems like I was working on the same solution as Zhouyi Zhang at the same time!

1 of 1 people found this helpful
• ###### 6. Re: Measure Values as % of One Particular Measure Value

Thanks Ossai

ZZ

• ###### 7. Re: Measure Values as % of One Particular Measure Value

Thank you very much Ossai!  This was very helpful.

• ###### 8. Re: Measure Values as % of One Particular Measure Value

Thank you!!!  I'm marking this one as correct since it includes the workbook link.

• ###### 9. Re: Measure Values as % of One Particular Measure Value

Thank you so much for your help Zhouyi!

1 of 1 people found this helpful
• ###### 10. Re: Measure Values as % of One Particular Measure Value

You're welcome Emily. I'm glad it helped.