Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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
Solved! Go to Solution.
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.
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.
for the blank values, that's okay as they will be filled in later, so blank values should be ignored
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
23 | |
21 | |
20 | |
13 | |
12 |
User | Count |
---|---|
43 | |
31 | |
24 | |
23 | |
22 |