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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Anonymous
Not applicable

Power BI Average is different from Excel Average

I have a table which shows the availablilty of 2 Entities (2 locations for each) for every hour for the last 35 days. PFB a screensnip of the table.

sachinjt_1-1663267523054.png

(the availablity values are not always 100. It is a decimal value anywhere between 0 and 100)

 

I want to use 2 cards to show the average availability of the 2 entities. I tried 2 different methods for this:

Method 1:

sachinjt_2-1663267960447.png

The average is 100

 

Method 2:

I used a measure to find the average.

Avg1 = CALCULATE(AVERAGE('browser data'[Availability]),'http data'[Entity ID] = "HTTP_CHECK-12CD3A6E8A36AA18")
The average is again 100.
 
But the actual average of this is not 100. The excel average for the same entity is 98.91
sachinjt_4-1663268288930.png

 

I have found out that the way in which excel and Power BI calculate average is different but I not quite able to figure out how to use averagex function get the desired result.

 

Can anyone help please.

1 ACCEPTED SOLUTION
ahmadibrahimbus
Resolver III
Resolver III

Dear @Anonymous ,

Power BI calculate Average as an (arithmetic mean) of all the numbers in the specified column,However when you are using the average function in the pivot table you are calculating the average of average.

so, if you want to have the same calculation as excel,use the below formula:

AveragePerCity =

AVERAGEX(

VALUES('browser data'[Availability]), 

    CALCULATE(AVERAGE('browser data'[Availability]),'http data'[Entity ID] = "HTTP_CHECK-12CD3A6E8A36AA18"))

hope this helps .

please mark it as a solution if it's solved.

View solution in original post

1 REPLY 1
ahmadibrahimbus
Resolver III
Resolver III

Dear @Anonymous ,

Power BI calculate Average as an (arithmetic mean) of all the numbers in the specified column,However when you are using the average function in the pivot table you are calculating the average of average.

so, if you want to have the same calculation as excel,use the below formula:

AveragePerCity =

AVERAGEX(

VALUES('browser data'[Availability]), 

    CALCULATE(AVERAGE('browser data'[Availability]),'http data'[Entity ID] = "HTTP_CHECK-12CD3A6E8A36AA18"))

hope this helps .

please mark it as a solution if it's solved.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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