Forum Discussion

MAAbdullah47's avatar
MAAbdullah47
Icon for Helper V rankHelper V
9 years ago
Solved

Quick Measure percentage difference gives wrong value

Dear All,   Thank you for your support, I need your advice on the following issue:   I want to calculate the average price difference between car model year and the previous one, I created a quic...
General Comment
Need Help
  • v-ljerr-msft's avatar
    v-ljerr-msft
    9 years ago

    Hi MAAbdullah47,

     

    I just verified that the formula works all fine with your shared sample data.

     

    Avg price for 2016 = 219270 and for 2015 = 219058 so the equation should be

     

    Diff = (219270 / 219058 ) - 1 = 0.1 %

     

     

    Regards

  • v-ljerr-msft's avatar
    v-ljerr-msft
    9 years ago

    Hi MAAbdullah47,

     

    Based on my test, the formula below should work. :smileyhappy:

    Diff = 
VAR currentYear =
    MAX ( 'saudisale_c'[d_year] )
VAR avgPricePY =
    CALCULATE (
        AVERAGE ( 'saudisale_c'[d_price] ),
        FILTER ( ALL(saudisale_c[d_year]), 'saudisale_c'[d_year] = currentYear - 1 )
    )
RETURN
    DIVIDE ( AVERAGE ( 'saudisale_c'[d_price] ) - avgPricePY, avgPricePY )

     

    Regards

v-ljerr-msft's avatar
v-ljerr-msft
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago

Hi MAAbdullah47,

 

If I understand you correctly, the formula below should work in your scenario. :smileyhappy:

 

Diff =
VAR currentYear =
    MAX ( 'saudisale_c'[d_year] )
VAR avgPricePY =
    CALCULATE (
        AVERAGE ( 'saudisale_c'[Price] ),
        FILTER ( ALL ( 'saudisale_c' ), 'saudisale_c'[d_year] = currentYear - 1 )
    )
RETURN
    DIVIDE ( AVERAGE ( 'saudisale_c'[Price] ) - avgPricePY, avgPricePY )

 

 

Regards

MAAbdullah47's avatar
MAAbdullah47
Icon for Helper V rankHelper V
9 years ago

I think the equation not true, again the equation as the following example:

 

 

Avg price for 2016 = 214,250 and for 2015 = 181,676 so the equation should be

 

Diff = (214,250 / 181,676) - 1 = 17.93 %