Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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:...
  • SpartaBI's avatar
    SpartaBI
    4 years ago

    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


         

SpartaBI's avatar
SpartaBI
Community Champion
4 years ago

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


     

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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

  • SpartaBI's avatar
    SpartaBI
    Community Champion
    4 years ago

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