weighted average at the subgroup & group level while keeping the value at the row level

[BMltc]

Hi team,

I have a dataset like this

Category	Sub Category	Weight	Value
A	A1	0,2	5
A	A2	0,1	3
B	B1	0,3	2
B	B2	0,1	4
B	B3	0,3	5

and i want to create a matrix with categories & sub categories and with the weighted average while keeping the value at the row level as seen in the "desired output" column

Category	Sub Category	Weight	Value	Weight x Value	Desired output
A					4,33
	A1	0,2	5	1	5
	A2	0,1	3	0,3	3
B					3,57
	B1	0,3	2	0,6	2
	B2	0,1	4	0,4	4
	B3	0,3	5	1,5	5
TOTAL					3,8

with 4.33 = (3*0.1+5*0.2)/(0.1+0.2)

many thanks

[Jihwan_Kim · Accepted solution]

Hi, @BMltc 

Please check the below picture and the sample pbix file's link down below.

Desired Output measure =
IF (
ISINSCOPE ( 'Table'[Sub Category] ),
SUMX ( 'Table', 'Table'[Value] ),
CALCULATE (
SUMX ( 'Table', 'Table'[Weight] * 'Table'[Value] ),
ALLEXCEPT ( 'Table', 'Table'[Category] )
)
/ CALCULATE (
SUMX ( 'Table', 'Table'[Weight] ),
ALLEXCEPT ( 'Table', 'Table'[Category] )
)
)

https://www.dropbox.com/s/o2flu2oirbhk9d7/bmltc.pbix?dl=0