Urgent overall average by category

[Anonymous]

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

[mwimberger · Accepted 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:

[Anonymous]

Thanks a lot! It worked perfectly!!