Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare 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
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
29 | |
14 | |
11 | |
10 | |
9 |