cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## Wrong average

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?

1 ACCEPTED SOLUTION
Community Champion

@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])

Showcase Report – Contoso By SpartaBI

6 REPLIES 6
Frequent Visitor

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

Helper I

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

Community Champion

@IgorAM my pleasure

Community Champion

@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])

Showcase Report – Contoso By SpartaBI

Helper I

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

Community Champion

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors