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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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