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
    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 )

    Hi ,

     

    It works without problems, but when I use it in the report it gives the following error (please review the snapshot below):

     

    I think this is because of the (Year) is text, shall it works if I convert it to Number (Whole Number)?

     

     

     

     

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

      Hi MAAbdullah47,

       

      Yes, the [d_year] column needs to be Number (Whole Number) type. Then it should work. :smileyhappy:

       

      Regards

      • MAAbdullah47's avatar
        MAAbdullah47
        Helper V
        9 years ago

        Ok , I'll convert and Check.

  • 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.