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
Jasonvl
Frequent Visitor

Average in Excel Does Not Equal Average in PBI?

Hi,

 

I have 2 numbers, 73.08 & 65.52 which when I add together in Excel and divide by 2 to get the average I get 69.30.

 

However in PBI using the same 2 numbers I get 69.09

 

The measure in PBI is a calculation of a number of rows that equal "Y" displayed as a % 

 

The individual rows for the 2 results display the correct numbers but the grand total row which I'm expecting to see the average doesn't?

 

My calculation is as follows - 

 

GLD = (COUNTROWS(filter('1 Pupil_Assessments','1 Pupil_Assessments'[Result]="Y")))/(COUNTROWS('1 Pupil_Assessments'))

 

Any ideas why this might be please?

 
Regards, Jason

 

1 ACCEPTED SOLUTION
LaurentCouartou
Solution Supplier
Solution Supplier

Hi,


69.30 is the result of (73.08 + 65.52)/2.

What you see in Power BI as a sub-total is the total number of successful assessments over the total number of assessments.

I think the last result should be the correct way to calculate this, but you may have other reasons to do otherwise.

Regards

View solution in original post

3 REPLIES 3
LaurentCouartou
Solution Supplier
Solution Supplier

Hi,


69.30 is the result of (73.08 + 65.52)/2.

What you see in Power BI as a sub-total is the total number of successful assessments over the total number of assessments.

I think the last result should be the correct way to calculate this, but you may have other reasons to do otherwise.

Regards

Thanks @LaurentCouartou that explained perfectly the difference I was getting and I understand now.

 

I think 69.09 is the answer I want but I need to speak to my colleagues and thanks @Anonymous for your tip.

 

Jason.

Anonymous
Not applicable

@Jasonvl 

When things like this happen it usually has to do with a filter situation. Meaning, what you expect to see and what DAX is showing are not the same because of what is happening in the background. Any chance you can upload some sample data?

 

Also, be sure to use DIVIDE instead of /, since DIVIDE has built-in error catching

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.