topic Re: Sum of Average of each subcategory in Visual Table in DAX Commands and Tips

Sum of Average of each subcategory in Visual Table

gaLaii — Fri, 27 Jan 2023 16:50:00 GMT

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

Re: Sum of Average of each subcategory in Visual Table

amitchandak — Sat, 28 Jan 2023 02:41:31 GMT

gaLaii , Try a measure like

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

Re: Sum of Average of each subcategory in Visual Table

Anonymous — Mon, 30 Jan 2023 09:46:26 GMT

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.

Re: Sum of Average of each subcategory in Visual Table

gaLaii — Mon, 30 Jan 2023 14:20:36 GMT

Thank you so much.. Appreciate it!!!