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 quick measure as the following:

 

Average of Price % difference from 2016 3 =
VAR __BASELINE_VALUE =
CALCULATE(
AVERAGE('saudisale_c'[Price]),
TREATAS({("2016")}, 'saudisale_c'[d_year])
)
VAR __MEASURE_VALUE =
AVERAGE('saudisale_c'[Price])
RETURN
IF(
NOT ISBLANK(__MEASURE_VALUE),
DIVIDE(__MEASURE_VALUE - __BASELINE_VALUE, __BASELINE_VALUE)
)

 

Car Year ModelQuick Measure

To simplify the math Avg price for 2016 = 214,250 and for 2015 = 181,676 so the formula should be

 

Diff = (214,250 / 181,676) - 1 = 17.93 %, the Above DAX formula (quick measure) give the

wrong number can any one help me what shall I do change the Dax formula to calculate the difference correctly?

 

Thank You In Advanced

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

23 Replies

  • 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

      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