Sum of Average of each subcategory in Visual Table

[gaLaii]

Hi

I have a table with  categories and subcategories. i want to display i power BI visual table the average of the subcategories but display the sum of the average subcategories  per category. here is the sample result and sample table source below. 

Sample Table 

Date	Category	Sub-Category	Score
10-Jan	Category 1	Sub-Category 1	1
10-Jan	Category 1	Sub-Category 2	5
10-Jan	Category 1	Sub-Category 3	5
10-Jan	Category 1	Sub-Category 4	5
10-Jan	Category 1	Sub-Category 5	4
10-Jan	Category 1	Sub-Category 6	5
10-Jan	Category 2	Sub-Category 1	5
10-Jan	Category 2	Sub-Category 2	2
10-Jan	Category 2	Sub-Category 3	1
10-Jan	Category 2	Sub-Category 4	2
10-Jan	Category 3	Sub-Category 1	5
10-Jan	Category 3	Sub-Category 2	1
10-Jan	Category 4	Sub-Category 1	4
10-Jan	Category 4	Sub-Category 2	2
10-Jan	Category 4	Sub-Category 3	4
10-Jan	Category 4	Sub-Category 4	5
10-Jan	Category 4	Sub-Category 5	2
11-Jan	Category 1	Sub-Category 1	5
11-Jan	Category 1	Sub-Category 2	4
11-Jan	Category 1	Sub-Category 3	3
11-Jan	Category 1	Sub-Category 4	5
11-Jan	Category 1	Sub-Category 5	4
11-Jan	Category 1	Sub-Category 6	2
11-Jan	Category 2	Sub-Category 1	4
11-Jan	Category 2	Sub-Category 2	1
11-Jan	Category 2	Sub-Category 3	2
11-Jan	Category 2	Sub-Category 4	3
11-Jan	Category 3	Sub-Category 1	5
11-Jan	Category 3	Sub-Category 2	5
11-Jan	Category 4	Sub-Category 1	1
11-Jan	Category 4	Sub-Category 2	5
11-Jan	Category 4	Sub-Category 3	4
11-Jan	Category 4	Sub-Category 4	4
11-Jan	Category 4	Sub-Category 5	5

Desired Result in power bi

[Anonymous · Accepted solution]

Hi @gaLaii ,

With amitchandak's measure, you will get correct result in subtotal, however incorrect in Total.

You can try my code to create a measure.

Sum of Avg =
VAR _SUMMARIZE =
    SUMMARIZE (
        'Table',
        'Table'[Category],
        'Table'[Sub-Category],
        "Avg", AVERAGE ( 'Table'[Score] )
    )
RETURN
    SUMX ( _SUMMARIZE, [Avg] )

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[gaLaii]

Thank you so much.. Appreciate it!!!

[amitchandak]

@gaLaii , Try a measure like

Sumx(Values(Table[Sub Category]), calculate(Average(Table[Score]) ) )