5 Replies Latest reply on Dec 15, 2016 1:19 AM by Mahfooj Khan

# How to compute % of 2 variables using LOD

Hi All,

I encountered problem computing % of 2 variables when the 2 variables are selecting different stage in their 'if' statement.

1st variable: { INCLUDE [Stage]:IF MIN([Stage])='Won' THEN COUNTD([Order No]) END}

2nd variable: { INCLUDE [Stage]:IF MIN([Stage])='Lost' THEN COUNTD([Order No]) END}

What I want to get is the % ratio of 1st variable. I wrote the following formula but it gives me null result. Anyone can help? Thanks!

[1st variable]/([1st variable]+[2nd variable])

Emily

• ###### 1. Re: How to compute % of 2 variables using LOD

Hey Emily,

Are your two original formulas giving the desired output? Whenever I do conditionals inside an LOD calc, I put the IF statement inside the aggregation (in this case, COUNTD):

{ INCLUDE [Stage]:COUNTD(IF MIN([Stage])='Won' THEN [Order No] END)}

{ INCLUDE [Stage]:COUNTD(IF MIN([Stage])='Lost' THEN [Order No] END)}

For the above formulas, I'm not sure whether you would need MIN([Stage]) = 'Win or Lost' or just [Stage]='Win' or 'Lost'

I'm thinking if these formulas are modified, then your third formula might return the correct value.

Let us know how this works for you!

Walt

• ###### 2. Re: How to compute % of 2 variables using LOD

Hi Emily,

I don't know why you've used INCLUDE{} here. Just asking.

As per my understanding below formula can serve your purpose. Try this

1st variable: COUNTD(IF [Stage]='Won' THEN [Order No] END)

2nd variable: COUNTD(IF [Stage]='Lost' THEN [Order No] END)

Let me know If this help.

Mahfooj

• ###### 3. Re: How to compute % of 2 variables using LOD

Hi Walt,

Yes the two original formulas gave me the desired output. After change, the result for the 3rd formula is not correct. I tried Mahfooj's suggestion and it works.

Thanks Walt!

Emily

• ###### 4. Re: How to compute % of 2 variables using LOD

Hi Mahfooj,

Yes no need to use INCLUDE{} here. Your method works. Thanks a lot!

Emily

• ###### 5. Re: How to compute % of 2 variables using LOD

You are welcome!

Mahfooj