Creating Dax Formula to Sum the Average at different levels and display in a matrix

[Anonymous]

Hi,

I am currently running into a wall quite severely with two measures I am trying to create. The source table looks like follows. PersonId, which indicates a person, brand which indicates a brand, category which indicates a question for a category for the brand, and score which is 1 for agree or 0 for disagree.

PersonId	Brand	Category	Score
1	1	1	1
1	1	2	0
1	1	3	1
1	1	4	0
1	1	5	0
1	2	1	1
1	2	2	1
1	2	3	0
1	2	4	1
1	2	5	0
2	1	1	1
2	1	2	0
2	1	3	1
2	1	4	0
2	1	5	1
2	2	1	0
2	2	2	1
2	2	3	1
2	2	4	0
2	2	5	0

What I am trying to do is create a matrix which has on the y axis the category and x axis the brand, with the average score for that category, the sum of the average scores for all brands, and the sum of the average for all categories, the output would look as follows:

	BrandId	1	1	1	2	2	2
Category		Actual Average	Sum of Average all Brands	Sum of Average All Categories
1		1	1 (Brand 1) + 0.5 (Brand 2) = 1.5	1 (Cat 1) + 0 (Cat 2) + 1 (Cat 3) + 0 (Cat 4) + 0 (Cat 5) = 2
2			2 (Brand 1) + 0.5 (Brand 2) = 1.5	2 (Cat 1) + 0 (Cat 2) + 1 (Cat 3) + 0 (Cat 4) + 0 (Cat 5) = 2
3			3 (Brand 1) + 0.5 (Brand 2) = 1.5	3 (Cat 1) + 0 (Cat 2) + 1 (Cat 3) + 0 (Cat 4) + 0 (Cat 5) = 2
4			4 (Brand 1) + 0.5 (Brand 2) = 1.5	4 (Cat 1) + 0 (Cat 2) + 1 (Cat 3) + 0 (Cat 4) + 0 (Cat 5) = 2
5			5 (Brand 1) + 0.5 (Brand 2) = 1.5	5 (Cat 1) + 0 (Cat 2) + 1 (Cat 3) + 0 (Cat 4) + 0 (Cat 5) = 2

I have tried a lot of different things, such as group by, but summarizecolumns/group by is not allowed at a row level context (not sure why) but have not been able to find a work around to do this without group by (see code for group by which i tried below) - if someone could help me translate this into measures which do not use summarize and allow me to put it into a row level context I would be very grateful!

attemped code using summarize which does not work at row level in tables:

VAR ScoreByAttribute = SUMMARIZECOLUMNS (
Table[Category],
"AttributeScore", AVERAGE(Table[Score]))

RETURN
SUMX( ScoreByAttribute , [AttributeScore])

Cheers,

Deniz

[amitchandak · Accepted solution]

@Anonymous , try a measure like

Sumx(summarize(Table, Table[Brand], Table[Category], "_avg", Average(Table[Score])),[_avg])

[Anonymous]

Hi Amit, thanks for the response. Unfortunatel this does not provide the solution, as it still shows an average when the measure is thrown into a matrix, i would like for the total shown at the subtotal level to appear at all levels of the row. See below screenshot. Would you be able to help with that? Please see image below

[Anonymous]

Figured it out:

measuretestcategory = CALCULATE(Sumx(summarize(Table, BrandImageAttributes[BrandId], Table[Category], "_avg", Average(Table[Score])),[_avg]),ALLEXCEPT(Table,Table[Brand]))

I'll mark your solution as solved as it helped me in the right direction! thanks 🙂