Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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 Model
Quick 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
Solved! Go to Solution.
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
Hi @MAAbdullah47,
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
Hi @MAAbdullah47,
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
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 %
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.
Regards
Another Question Shall I Send with it the csv file in addition to power bi?
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.
Regards
But How to Attach files, It accepts only Images.
Hi @MAAbdullah47,
You can upload it to OneDrive or Dropbox first and then post the link in the private message.
Regards
Sorry Is it possible google-drive ?
Cuz It is the standard of our company and we should follow it ? If yes can you send me your email to share?
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
Please check your private message, when I do filtering It gives wrong results.
Hi @MAAbdullah47,
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
Thank You so much
Fine ,
It works now, Just need to adjust the Format Above, thank you for your support.
Also, another problem some years not labeled.
Please check above nothing changed.
Thank You, Can You Send me your email, please.
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)?
Hi @MAAbdullah47,
Yes, the [d_year] column needs to be Number (Whole Number) type. Then it should work.
Regards
User | Count |
---|---|
121 | |
69 | |
66 | |
56 | |
52 |
User | Count |
---|---|
182 | |
85 | |
67 | |
61 | |
53 |