Quick measure - Percentage difference

[MHongisto]

If I want to calculate percentage of costs vs sales amounts, I tried using the quick measure percentage difference. It gave me this code:

Measure1 % difference from Sales amount =
VAR __BASELINE_VALUE =
SUM('Sales'[Sales amount])
VAR __VALUE_TO_COMPARE =
'Sales'[Measure1]
RETURN
DIVIDE(__VALUE_TO_COMPARE - __BASELINE_VALUE; __BASELINE_VALUE)

This gives a negative percentage. If I remove the substraction in the DIVIDE line to:

DIVIDE(__VALUE_TO_COMPARE; __BASELINE_VALUE)

The return is the correct percentage that I would get with:

Measure% = CALCULATE('Sales'[Measure1]) / SUM('Sales'[Sales amount]) *100

Is this an issue with me not understanding what "Percentage difference" is meant to do or is this something else?

[v-jiascu-msft · Accepted solution]

Hi @MHongisto,

"Percentage difference" is under the submenu "Mathematical operations". It looks like a math theory, which evaluates the difference and compare with the base value. Please refer to Percent of Increase or Decrease if you'd like to.

Best Regards!

Dale

[rahulsinha]

There's an issue when the base value is negative and the compared value is positive. In general, if the numerator indicates the direction, i.e. increase or decrease, then the denominator must have the absolute value. I am getting around the error in quick measure formula by adding ABS(__BASELINE_VALUE). 

--Thus changing

RETURN
DIVIDE(__VALUE_TO_COMPARE - __BASELINE_VALUE; __BASELINE_VALUE)

--to

RETURN
DIVIDE(__VALUE_TO_COMPARE - __BASELINE_VALUE; ABS(__BASELINE_VALUE))

[MHongisto]

Thank you! So it was an issue with me not understanding math or English.

[cs_skit]

Percentage type just displays 0.7 or 1.5 as 70 % or 150%

So DIVIDE(Sales;Base) is correct if your sales are 1500 and your base is 1000 result is 1.5 so displayed as 150% when you change it to Percentage

[MHongisto]

The Quick measure - Percentage difference calculation automatically puts the subtraction in DIVIDE. I don't understand why. Bug or feature?

[cs_skit]

Its a Percentage DIFFERENCE

Its just a recommendation.

It makes sense in many scenarios like:

Base 1000 Value 1020

Quick measure gives you 2%

Does not mean that its always correct in your specific scenario if you don't want 2% but 102% then you don't subtract.

Theres no right and wrong here depends on what you need.