Forum Discussion

MHongisto's avatar
MHongisto
Frequent Visitor
9 years ago
Solved

Quick measure - Percentage difference

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?

 

Need Help

6 Replies

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    9 years ago

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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's avatar
      MHongisto
      Frequent Visitor
      9 years ago

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

  • cs_skit's avatar
    cs_skit
    Resolver IV
    9 years ago

    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's avatar
      MHongisto
      Frequent Visitor
      9 years ago

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

      • cs_skit's avatar
        cs_skit
        Resolver IV
        9 years ago

        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.