Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to Solution.
@IgorAM 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])
I'm, now its working, thank you!!!
@IgorAM 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])
still doesnt work, I used the unit column from fact table
@IgorAM you didn't put the calculate around the average. Look at the measure I wrote. Don't forget to accept the solution 🙂
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
43 | |
23 | |
21 | |
15 | |
15 |
User | Count |
---|---|
45 | |
31 | |
30 | |
18 | |
17 |