Forum Discussion

MAAbdullah47's avatar
MAAbdullah47
Helper 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
Microsoft 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
Helper 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 %

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

    Hi MAAbdullah47,

     

    Based on my understanding, the formula should work in your scenario.

     

    So could you share a sample pbix file which can reproduce the issue, so that I can help further investigate on it? You can upload it to OneDrive or Dropbox and post the link here or send it to me in private message. Do mask sensitive data before uploading. :smileyhappy:

     

    Regards

    • MAAbdullah47's avatar
      MAAbdullah47
      Helper V
      9 years ago

      Thank You, Can You Send me your email, please.

    • MAAbdullah47's avatar
      MAAbdullah47
      Helper V
      9 years ago

      Another Question Shall I Send with it the csv file in addition to power bi?

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

        Hi MAAbdullah47,

         

        You can send a private message to me within the forum(Messages > Compose New Message) like below. And it's better that you can also share the csv files. :smileyhappy:

         

         

        Regards