Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
MAAbdullah47
Helper V
Helper V

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 ModelCar Year ModelQuick MeasureQuick 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

2 ACCEPTED SOLUTIONS

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 %

 

r4.PNG

 

Regards

View solution in original post

Hi @MAAbdullah47,

 

Based on my test, the formula below should work. Smiley Happy

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

View solution in original post

23 REPLIES 23
v-ljerr-msft
Employee
Employee

Hi @MAAbdullah47,

 

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

 

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. Smiley Happy

 

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. Smiley Happy

 

message.PNG

 

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. Smiley Happy

 

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 %

 

r4.PNG

 

Regards

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

 

 

Err3.png 

 

 

 

Hi @MAAbdullah47,

 

Based on my test, the formula below should work. Smiley Happy

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

Thank you I Ll check and get back to u

Fine ,

 

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

Also, another problem some years not labeled.

Error 3.png

 

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

 

Error In Year difference.png

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. Smiley Happy

 

Regards

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.