topic Re: Wrong average in DAX Commands and Tips

Wrong average

Anonymous — Sat, 14 May 2022 18:05:46 GMT

Hi community,

I'm having a problem to calculate a average.
There's two tables, a fact and a dimension (with goals), I made a relationship between them to get the goals.

Fact table is like that:

Unit	Region	Real	Goal (this is a calculated column)
BEL	S	1.2	1.0
CGR	S	0.9	0.8
GYN	S	0.73	1.0
RBR	W	0.89	0.8
EST	E	1.45	0.8
MCP	S	0.67	0.8
STM	S	0.49	0.8
LEM	S	1.53	0.8
LAR	W	0.89	0.8
SLT	W	0.89	0.8
BUR	W	0.89	1.0
RIO	E	1.45	0.8
SMP	W	0.89	1.0
SHE	E	1.45	0.8

Then, I used this Dax formula:

AVERAGE(fact_table[Goals])

And I got this when I use some filter for Region, in this case region "S":

The correct average should be: (2*1 + 4*0.8)/6 = 0.8667

What is happening?

Re: Wrong average

SpartaBI — Sat, 14 May 2022 18:33:11 GMT

Anonymous the total of the matrix is not the average of the rows in it. It's the average of the underlying data without filters. You have different number of rows per unit. The total is the weighted average.
If what you want is the average of averages than write this:

Measure = AVERAGEX( VALUES(fact_table[Unit]), CALCULATE(AVERAGE(fact_table[Goals])) )

In case you put on the rows of the matrix the unit column from the dimension then swith in my function the VALUES(fact_table[Unit]) to VALUES(dim_table[Unit])

Showcase Report – Contoso By SpartaBI

Re: Wrong average

Anonymous — Sat, 14 May 2022 20:24:55 GMT

still doesnt work, I used the unit column from fact table

Re: Wrong average

SpartaBI — Sat, 14 May 2022 20:28:35 GMT

Anonymous you didn't put the calculate around the average. Look at the measure I wrote. Don't forget to accept the solution 🙂

Re: Wrong average

Anonymous — Sat, 14 May 2022 20:58:21 GMT

I'm, now its working, thank you!!!

Re: Wrong average

SpartaBI — Sat, 14 May 2022 21:01:55 GMT

Anonymous my pleasure

Re: Wrong average

Andras1 — Fri, 17 May 2024 14:38:09 GMT

Many thanks, SpartaBI! You solved a problem I have been struggling with for days.