Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
User | Count |
---|---|
53 | |
45 | |
19 | |
16 | |
14 |
User | Count |
---|---|
108 | |
56 | |
29 | |
21 | |
17 |