Problem detected with average between PowerBI and Excel

[Syndicate_Admin]

Hello tod@s,

I just found a difference in the data calculated by PowerBI and Excel that I don't quite understand. I have a very basic table of 19 records. In this table I have a column with decimal values (2 decimal places). Based on this table, if I tell you to show me the average it tells me that it is 2.68. However, if I export this table and take it to Excel it tells me that its average is 2.51 ???

I have checked that all the data is exactly the same and there is no difference. I've also checked that the number format in both PowerBI and Excel is 2 decimal places.

Thank you.

[Syndicate_Admin · Accepted solution]

After looking a little more closely, I saw where the problem was. It turns out that the table generated from a chart was incomplete. That is, PowerBI groups records with the same value. For that reason, the averages that PowerBI displays (over the model total) do not correspond to those in Excel.

To fix the problem, in the PowerBI table I've added an index column so that it doesn't group by values that are equal. From here, I could see that in total I had not 19 records but 58.

Detected the problem, I have exported again the table with all the records and now the promdio that shows me PowerBI and Excel is extamente the same.

Thank you.

[amitchandak]

@Syndicate_Admin , In excel, is average coming as pre-calculated and wrong. or are you doing simple average or rows.

Because ideally grand total should be recalculated based on a formula, it should not be a simple avg.

if power bi export contains a grand total and that is wrong(means it does not match with what is shown I power bi) , then log an issue - https://community.powerbi.com/t5/Issues/idb-p/Issues

[Syndicate_Admin]

Hi @Syndicate_Admin. Thank you very much for replying.

As you indicate, the average that Excel makes is not correct? In the end, I understand the average should be the same on both platforms not? Logically taking into account the same data structure and format.

I attach the example I am testing in case pos can serve as an example.