Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello guys!
I need to know how to calculate the overall average of categories grouped by IDs.
Description:
-Category A should return Points = 2 because there are 4 different IDs (8940,8930,8936 and 8939)
-Category B should return Points = 8 (3+5) because there are 2 different IDs (8285 and 8280)
-Category C should return Points = 9,5 (2,5+3+4) because there are 3 different IDs (8725,8745 and 8737)
Once the sums are calculated by categories, now I must sum all these values and divide by 3 (number of distinct categories).
Result = (2+8+9,5)/3 = 6,5 (I need this value)
Points | Item Id | category |
0,5 | 8940 | A |
0,5 | 8930 | A |
0,5 | 8936 | A |
0,5 | 8939 | A |
3 | 8285 | B |
3 | 8285 | B |
5 | 8280 | B |
5 | 8280 | B |
2,5 | 8725 | C |
2,5 | 8725 | C |
3 | 8745 | C |
3 | 8745 | C |
4 | 8737 | C |
4 | 8737 | C |
Solved! Go to Solution.
Hi @Anonymous
Try this :
Create a summary table :
Summary Table =
SUMMARIZE (
'Table',
'Table'[category],
'Table'[Item Id],
"average of ID", AVERAGE ( 'Table'[Points] )
)
Then create a measure of the average of the sum of the averages:
Average of summary =
DIVIDE (
SUM ( 'Summary Table'[average of ID] ),
DISTINCTCOUNT ( 'Summary Table'[category] )
)
This will give you the result you're after:
Hi @Anonymous
Try this :
Create a summary table :
Summary Table =
SUMMARIZE (
'Table',
'Table'[category],
'Table'[Item Id],
"average of ID", AVERAGE ( 'Table'[Points] )
)
Then create a measure of the average of the sum of the averages:
Average of summary =
DIVIDE (
SUM ( 'Summary Table'[average of ID] ),
DISTINCTCOUNT ( 'Summary Table'[category] )
)
This will give you the result you're after:
Thanks a lot! It worked perfectly!!
User | Count |
---|---|
90 | |
74 | |
67 | |
63 | |
55 |
User | Count |
---|---|
101 | |
92 | |
74 | |
60 | |
59 |