I recently came across an issue where Total average in Power BI shows different value in Excel for the same data, which seems to be incorrect in Power BI. In today’s post, we will discuss how Power BI and Excel calculate Averages.

• 

• 

Power BI calculate Average as an (arithmetic mean) of all the numbers in the specified column. Example; the following function “= AVERAGE(Shipping[Cost])” returns the average of the values in the column Cost in Shipping table. The function takes the specified column as an argument and finds the average of the values in that column.

Excel calculate Average as an (arithmetic mean) of an arguments. Example; the following function “=AVERAGE(C3:C19)” returns the average of the numbers in that range.

Because we want Total average of an expression Average Cost, we create a measure using AVERAGEX function to get the correct result in Power BI to match the result in Excel.

AVERAGEX calculates the average (arithmetic mean) of a set of expressions evaluated over a table.

AveragePerCity =

AVERAGEX(

VALUES(City[ShipCity]), 

    CALCULATE([Average Cost]))

The VALUES function create a list of unique ShipCity and the AVERAGEX function evaluate the expression for each row in the City table, then take the resulting set of the values to calculate its arithmetic mean of the expression.

Now you can see the Average for Power BI and Excel shows the same result below.

• 

• 

Happy data modeling 🙂