topic Re: Average of average measure in DAX Commands and Tips

Average of average measure

Chrisjr — Wed, 07 Feb 2024 19:22:54 GMT

Hello All,

I have the below table

N Group	Matricule Dealer	Wave	sum score	AVG all waves
500	ER1022	1	40	44,50
500	ER1022	2	49	44,50
500	ER1700	1	56	56
500	ER1700	2		56
200	ER1080	1	64	64
200	ER1080	2		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 Group	Matricule Dealer	Wave	sum score	AVG all waves	AVG of AVG all waves
500	ER1022	1	40	44,50	50,25
500	ER1022	2	49	44,50	50,25
500	ER1700	1	56	56	50,25
500	ER1700	2		56	50,25
200	ER1080	1	64	64	64
200	ER1080	2		64	64

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

How could I achive this ?

Thanks

Re: Average of average measure

lbendlin — Thu, 08 Feb 2024 01:25:48 GMT

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.

Re: Average of average measure

Chrisjr — Thu, 08 Feb 2024 08:38:42 GMT

many thanks lbendlin

Re: Average of average measure

Chrisjr — Thu, 08 Feb 2024 08:58:07 GMT

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