Calculated Column - Diff between Current Row Value and Conditional Lookup Row Value

[Anonymous]

Hi,

I'm trying to create a Calculated Column in PowerBI that provides me with the result shown in Table A, Column "ActiveWeight_vs_AAA" below. 

This is a simple conditional sumif calculation: security weight in the current row minus security weight in portfolio AAA. If I was using Excel I would write and drag down the formula as:

=C2-SUMIFS($C:$C, $A:$A,"AAA", $B:$B, B2)

Examples:

(1) In Table A/Row 2, [ActiveWeight_vs_AAA] = 5%-5% = 0%.

(3) In Table A/Row 4, [ActiveWeight_vs_AAA] = 8%-5% = 3%.

(3) In Table A/Row 6, [ActiveWeight_vs_AAA] = 18%-0% = 18%.

It feels like it should be very straight forward but I am struggling. Any thoughts would be sincerely appreciated. 

(Also any advice on Calculated Column vs. Measure would be useful - for instance, it would be great to have Portfolio AAA controlled by a portfolio dropdown slicer in the PowerBI dashboard).

Table A

Portfolio	SecurityID	Weight	ActiveWeight_vs_AAA
AAA	bond_1	5%	0%
BBB	bond_1	1%	-4%
CCC	bond_1	8%	3%
DDD	bond_1	10%	5%
BBB	bond_2	18%	18%
CCC	bond_2	15%	15%
DDD	bond_2	10%	10%

[ChandeepChhabra]

@Anonymous 

For making it dynamic through a slicer, please refer to the Power BI file

Hope this helps

Thanks

[ChandeepChhabra · Accepted solution]

@Anonymous 

Try this column formula

Column = 
'Dataset'[Weight]-
SUMX(
    FILTER(
        ALL('Dataset'),
        'Dataset'[Portfolio]="AAA"&&
        'Dataset'[SecurityID]=EARLIER('Dataset'[SecurityID])
    ),
        'Dataset'[Weight]
)

Thanks