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
nsadams87xx
Helper III
Helper III

Measuring Average Delta

Hey everyone,

 

I'm having an unusual issue with calculating an average difference.  Basically, my goal is to calculate the 2 month average difference of the current year and last year of the length of stay in minutes patients stayed in the emergency room.  So my arithmatic would be:

 

((Average of Jan and Feb 2019) - (Average of Jan and Feb 2018))/(Average of Jan and Feb 2018)

 

My Power BI is as follows:

 

LOS Mean 2 Mo % Change = (AVERAGE('2 Month Timing Data'[los_mm]) - CALCULATE(AVERAGE('2 Month Timing Data'[los_mm]),SAMEPERIODLASTYEAR(Dates[the_date])))/CALCULATE(AVERAGE('2 Month Timing Data'[los_mm]),SAMEPERIODLASTYEAR(Dates[the_date]))
 
 

This is all fine but when I do the actual QA'ing of my formulas, I'm finding that Power BI is a touch off.  Power BI is only off by about .5% low but in this case where there are 60 or so dates to measure and anywhere between 5 to over 500 minutes per stay, my user says this is not acceptable.  I'm inclined to believe that too because my average for 2018 was 179 and for 2019 it was 182.  So with these potentially small differences, I can understand why we want very particular accuracy.

 

My "los_mm" column is formatted as a Decimal Number and its accuracy is to the hundreth.  The data in my data mart is all whole numbers.

 

Any suggestions?  I'm sorry, I cannot share my pbix file or a data sample due to the data being ultra sensitive/healthcare related.

1 ACCEPTED SOLUTION
nsadams87xx
Helper III
Helper III

Additional notes:

 

So (182 - 179)/179 = 1.68%

 

Power BI is calculating 1.29%.  So .5% off was a little aggressive, my apologies.

View solution in original post

3 REPLIES 3
nsadams87xx
Helper III
Helper III

Additional notes:

 

So (182 - 179)/179 = 1.68%

 

Power BI is calculating 1.29%.  So .5% off was a little aggressive, my apologies.

Issue resolved, there isn't actually an issue either.  I'm presenting my raw column as an "Average of LOS" and my calcuation is more accurate.  Power BI is doing exactly what it's told and accurately but within two different contexts in which my report is not accouting for in the presentation.  So both are right at the same time.  My user will have to decide how the report will be presented in a way that will make sense.

Smiley Happy

 

Best Regards
Maggie

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.