There is a critical difference between how Excel and DAX calculate averages.

Excel takes average of the rounded numbers in each row.

DAX (Power BI) calculates averages independently in each cell. Meaning that cell "total" is calculated not as average of rounded scores, but as average of non-rounded underlying values of the entire data set, which is then rounded. This is how DAX operates conceptually - each calculation is always done independently of other calculations in the table.

The way to fix it: In Power BI, rewrite your DAX formula to use AVERAGEX instead of AVERAGE. For example:

Correctly Averaged Scores = 
    AVERAGEX(
        VALUES(TableName[Submitter]),
        ROUND(CALCULATE(AVERAGE(TableName[OrbScore])),2)
)

Here, we first create a list of distinct "Submitters". Then we iterate over the list, and for each submitter calculate its average and round it to 2 digits. Finally, we calculate the average of the rounded averages, essentially replicating the behaviour of Excel.

Source:
PowerBI Percent Average Different Than Excel Average

  • No labels