Perform summation and average on subset of IDs based on occurence of attribute

[aloosh89]

Hello,

Consider the table below

ID	Attribute	Value
1	A	10
1	B	10
1	C	10
2	A	10
2	B	10
2	C	10
3	B	10
3	C	10
3	D	10

I would like to sum up the Vales of the column 'Value' but only for ID's for which the attribute 'A' appears. For the above table the desired output would be:

ID	Desired ouptut 1
1	30
2	30

And finally a second ouptut I want is to be able to sum this 'deisred output 1' and dived by the count of the IDs. Hence in this case (30+30)/2 = 30 = Desired ouptut 2

Thanks for any help in advance

Ali 

[Anonymous · Accepted solution]

Hi @aloosh89 ,

I suggest you to try code as below to create measures.

Desired ouptut 1 = 
VAR _IDLIST =
    CALCULATETABLE ( VALUES ( 'Table'[ID] ), 'Table'[Attribute] = "A" )
RETURN
    SUMX ( FILTER ( 'Table', 'Table'[ID] IN _IDLIST ), [Value] )

Desired ouptut 2 =
AVERAGEX ( VALUES ( 'Table'[ID] ), [Desired ouptut 1] )

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.

[amitchandak]

@aloosh89 , Try a measure like

Measure =
var _1 = countrows(filter(Table, Table[Attribute] ="A"))
return
Sumx(Filter(Values(Table[ID]), not(isblank(_1 ))), calculate(Sum(Table[Value])))