Forum Discussion
Average Function is wonky
I have a data set that is very granular. Invoice line level to be specific. I have a column that is the difference between two dates. I am taking an average of that date difference and am using it as a column. I have done this both as a measure and just a calculation in the table. My data rolls up from Yard to Region to Company, but what is strange and is not making sense is that the average of the yards does not equal the regions, and the regions do not equal the company. WHY is this? How do I fix it? For example, I have 2 Yards that make up a region. 11.77+17.03 = 28.8 /2 = 14.4 not the 14.06 that it is showing. I know that the dax is calculating at a cell level, and if I cannot change how it is calculating to match what end users will do in excel I need to be able to explain why.
First of all
An AVERAGE of AVERAGES is a mathematical blasphemy.
The average of 11.77 and 17.03 is indeed 28.8, but you don't want to calculate the average of those number, but sum the entire totals and divide by the total number of rows! IN other words "11.77" is not just a number, but it's made of several rows.
Then, the way PowerBi calculates total is sometimes strange (because of "Measure totals", see link below), but in this case it's perfectly fine. UNLESS you *really* want to calculate the "wrong average", and then read here:Measure Totals, The Final Word - Microsoft Power BI Community
4 Replies
First of all
An AVERAGE of AVERAGES is a mathematical blasphemy.
The average of 11.77 and 17.03 is indeed 28.8, but you don't want to calculate the average of those number, but sum the entire totals and divide by the total number of rows! IN other words "11.77" is not just a number, but it's made of several rows.
Then, the way PowerBi calculates total is sometimes strange (because of "Measure totals", see link below), but in this case it's perfectly fine. UNLESS you *really* want to calculate the "wrong average", and then read here:Measure Totals, The Final Word - Microsoft Power BI Community
So
Imagine that you have one row of category A with a value of 100
Then you have 50 rows of category B with a value of 1
The average of A will be 100, right?
And the average of B will be 1, right?
What you're saying is that the average of category A + B should be (1+100)/2 = 55.5?It is, in fact, VERY wrong.
Because PowerBi, when you are aggregating two different categories, does a correct calculation, so((100*1)+(1*50))/(1+50) = ((100)+(50))/51 = 2,94
As the filter context correctly consider all rows and calculates a correct average, summing all rows and dividing by the total number of rows.
I think you are looking for Avg or Avg. means avg to one level and Avg again.
Refer to this change sum to Avg in your case
https://community.powerbi.com/t5/Desktop/SUM-of-AVERAGE/td-p/197013
