6 Replies Latest reply on Jul 10, 2018 11:49 AM by Mavis Liu

How to get a percentage from two measures

Hello.

I have two measures that I can minus from each other and they give me the difference correctly but I can't divide or try to get percentage from. I am attaching an image as I can't attach a file with actual data due to confidentiality.

On Sunday I have number 125 and 198. I need to know the percentage difference between the two of them.

I created a test percentage field with

[Calc Current - Prior]/[Calc Prior 6 week DN Drop] and the first number for SUN is correct, -36% but the rest are not. Can not figure out why.

The calc I am using for the AGG(Calc Current- Prior) measure is SUM([Current Week DN Drop]) / ([Calc Prior 6 week DN Drop])

Is this a LOD issue? I have been studying up on them but still confusing on when to use FIXED, INCLUDE or EXCLUDE

Thanks in advance for any help.

EDIT. Attaching workbook with dummy data

• 1. Re: How to get a percentage from two measures

Hi Riaz,

It looks like your table calculation is being very strange and may be calculated using the wrong scope or direction. Should there be running sum table calculations applied to your 6 week current and 6 week previous fields?

Please could you provide us with a workbook/sample workbook with sample data? it's hard for us to figure out using only just a screenshot so a workbook is always the best option.

Thanks,

Mavis

• 2. Re: How to get a percentage from two measures

Hello

Thanks for your response. There should be a running sum total as I am trying to calculate a cumulative average. Let me fudge the numbers and Ill post a workbook.

• 3. Re: How to get a percentage from two measures

Hello Mavis Liu

• 4. Re: How to get a percentage from two measures

Hi Riaz,

So your calculation is working on a logical sense:

So your test percentage works out calc current - prior / calc prior 6 week DN Drop.

So -73/198 = -36.92%

then 38/243 = 16.69%

However, are you trying to work out the difference in the RUNNING TOTAL?

If you are, then that makes sense as to why only -36.92% is correct. It's because it's the start numbers where no running totals have been applied.

If you want it to be % difference of the running totals, then please create this calculation:

Now the % is shown as:

Is this what you were expecting? -35/441 is -7.91%.

Thanks and please see attached workbook.

Mavis

1 of 1 people found this helpful
• 5. Re: How to get a percentage from two measures

Thanks! That worked. Unfortunately it's not what they ended up wanting but it was a good lesson to learn.

Thanks for taking the time to answer my question.

• 6. Re: How to get a percentage from two measures

No problem! People always change their minds too, happens to me often!