cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Helper V

## Quick Measure percentage difference gives wrong value

Dear All,

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?

2 ACCEPTED SOLUTIONS
Employee

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

Employee

Based on my test, the formula below should work.

```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 23
Employee

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

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

Helper V

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 %

Employee

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.

Regards

Helper V

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

Employee

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.

Regards

Helper V

But How to Attach files, It accepts only Images.

Employee

You can upload it to OneDrive or Dropbox first and then post the link in the private message.

Regards

Helper V

Sorry Is it possible google-drive ?

Helper V

Cuz It is the standard of our company and we should follow it ? If yes can you send me your email to share?

Employee

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

Helper V

Please check your private message, when I do filtering It gives wrong results.

Employee

Based on my test, the formula below should work.

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

Helper V

Thank You so much

Helper V
Thank you I Ll check and get back to u
Helper V

Fine ,

It works now, Just need to adjust the Format Above, thank you for your support.

Helper V

Also, another problem some years not labeled.

Helper V

Helper V

Helper V
```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)?

Employee

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

Regards