cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
gjan1982 Frequent Visitor
Frequent Visitor

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

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

PortfolioSecurityIDWeightActiveWeight_vs_AAA
AAAbond_15%0%
BBBbond_11%-4%
CCCbond_18%3%
DDDbond_110%5%
BBBbond_218%18%
CCCbond_215%15%
DDDbond_210%10%

 

1 ACCEPTED SOLUTION

Accepted Solutions
ChandeepChhabra Established Member
Established Member

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

@gjan1982 

Try this column formula

 

 

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

 

Picture1.png

 

Thanks

 

2 REPLIES 2
ChandeepChhabra Established Member
Established Member

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

@gjan1982 

Try this column formula

 

 

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

 

Picture1.png

 

Thanks

 

ChandeepChhabra Established Member
Established Member

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

@gjan1982 

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

 

Picture1.png

 

Hope this helps

Thanks