POWERBI KEEPS GIVING ME WRONG AVERAGES WHY?

[grudginluv20]

Hello redditors,

I have what appears to be a simple problem that I cant figure a way out.

I simply have a table with values (0-100). The average of these values should be 93.14 as I had tested in excel.

However on powerBI, the average of these same values are 92.46.

This averaging mismatch affects other data streams that I am working on.

See the data in the image in both excel and powerBI:

POWERBI AVERAGES

EXCEL AVERAGES

[Anonymous]

Hello,Knox_DataMan ,jdbuchanan71 ,Selva-Salimi and MNedix ,thanks for your concern about this issue.

All of your answers are excellent!
And I would like to share some additional solutions below.
Hi,@grudginluv20 .I am glad to help you.

According to your description, you mentioned that in Power BI visual, there is a discrepancy in the data when the system automatically calculates Average.
The Total column ends up being calculated incorrectly is a common problem in Power BI.
The possible reasons are as follows:
The column/row Total has no context, plus the calculation logic in the dax code itself does not simply use a single aggregate function directly, such as

column_test =SUM([column]);
measure=MAX([column]);

Generally this directly use a power BI aggregation function, Total column display data will also directly use the calculation logic are data for aggregation operations.

You could try to create some calculation logic simple measure to replace the original error data.
For example, using the AVERAGEX function
like this:

If you are able to share the pbix file that does not contain sensitive data and the corresponding code, then this helps to help you resolve your issue.

If you are unable to share your data file, hopefully the following article will help.
Fortunately, there exists a user with a similar query who solved the problem by using an iterative function. function.
URL:
Why Power BI totals might seem inaccurate - SQLBI
Solved: Card total/table total doesn't match with the data - Microsoft Fabric Community
 

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best Regards,

Carson Jian,

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MNedix]

How did you calculate the average in PowerBI? I did the same and I got the same result in both Excel and PBI. Are you sure you don't have a weird filter in there? Are the colums formatted correctly?

[Knox_DataMan]

From a mechanical point of view, do the summed values in Excel and PBI match?  That is, the total amount in Excel and PBI = 2328.35?   Also, confirm the count of values which should equal 25.  Are you applying any filters to the data set which could affect the sum or row count?

[jdbuchanan71]

What is the calculation that is populating the field in PowerBI? 

If you don't take into account the volumn behind each amount, your total averags will not be correct.  Here is an example where averageing averages is way off and showing the correct total:

[Selva-Salimi]

Hi @grudginluv20 

Are the data in this visual come from same table? relations can cause wrong average calculation.

If this post helps, then I would appreciate a thumbs up  and mark it as the solution to help the other members find it more quickly.

[jdbuchanan71]

In your excel file it looks like you are taking the average of the averages.
Averages can be misleading and inaccurate if they don't take into account the number of units that went into each average. This is because a single data point can disproportionately affect the outcome of an average of averages.

The total average in PowerBI is recalulating the average across the entire dataset which would be the correct answer.

[grudginluv20]

The Averaging I am trying to achieve is (x+x+x...../n), like add all the values then divide by count of values. Even if there is a 0, it counts as a value. I did a hand calculation and it looks like the excel is right and powerbi is wrong and thus the confusion