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

        What is Abouut putting (Format) in the Text of the DAX? If Yes can help me in writing it?

    • MAAbdullah47's avatar
      MAAbdullah47
      Helper V
      9 years ago

      Things Become better but please check the result in the tooltip it should be on 2016 17.93% and 2015 21.71 ...etc

       

      Another problem it does not label some years e.g.: 2017, 2015 (please check the below screenshot) 

       

      Thank You