Forum Discussion

Chrisjr's avatar
Chrisjr
Icon for Helper IV rankHelper IV
2 years ago
Solved

Average of average measure

Hello All, 

 

 

I have the below table

N GroupMatricule DealerWavesum scoreAVG all waves 
500ER102214044,50 
500ER102224944,50 
500ER170015656 
500ER17002 56 
200ER108016464 
200ER10802 64 

 

 

The mesure sum score is : SUM(Fact_AuditMasterfile[SCORE])

 

The measure AVG all waves is : CALCULATE (
    AVERAGEX (
        SUMMARIZE (
            Fact_AuditMasterfile,
            Lookup_Push[Wave],
            Lookup_Dealer[Matricule Dealer],
            Lookup_Dealer[N°Group],
            "avg", SUM ( Fact_AuditMasterfile[SCORE] )
        ),
        [avg]
    ),
    ALLEXCEPT ( Fact_AuditMasterfile, Lookup_Dealer[Matricule Dealer] )
)

 

 

 

What I am trying to achieve is now to get the average of the AVG all waves measure. per N group

 

The end result would be as below :

N GroupMatricule DealerWavesum scoreAVG all wavesAVG of AVG all waves
500ER102214044,5050,25
500ER102224944,50 50,25
500ER170015656 50,25
500ER17002 56 50,25
200ER10801646464
200ER10802 64

64

 

So for N Group 500, it woul be the average of 44.50+56

 

How could I achive this ? 

 

Thanks

  • lbendlin's avatar
    lbendlin
    2 years ago

    You cannot measure a measure. Instead, implement your desired business logic from scratch

     

     

     

     

    Avg all waves = 
CALCULATE(average('Table'[sum score]),REMOVEFILTERS('Table'[Wave]))

Avg of Avg of waves = 
var a = SUMMARIZE(allselected('Table'),[N Group],[Matricule Dealer],"s",[Avg all waves])
var b = SELECTEDVALUE('Table'[N Group])
RETURN averagex(filter(a,[N Group]=b),[s])

     

    Note: I do not like the way the blank values are ignored for the average, but that's probably just me.

     

3 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    2 years ago

    You cannot measure a measure. Instead, implement your desired business logic from scratch

     

     

     

     

    Avg all waves = 
CALCULATE(average('Table'[sum score]),REMOVEFILTERS('Table'[Wave]))

Avg of Avg of waves = 
var a = SUMMARIZE(allselected('Table'),[N Group],[Matricule Dealer],"s",[Avg all waves])
var b = SELECTEDVALUE('Table'[N Group])
RETURN averagex(filter(a,[N Group]=b),[s])

     

    Note: I do not like the way the blank values are ignored for the average, but that's probably just me.

     

    • Chrisjr's avatar
      Chrisjr
      Icon for Helper IV rankHelper IV
      2 years ago

      for the blank values, that's okay as they will be filled in later, so blank values should be ignored