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

 

Highlighted
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

Helpful resources

Announcements
GregDeckler

How to Get Your Question Answered Quickly

Power BI Super User, Greg Deckler, explains

Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Ideas
Users Online
Currently online: 40 members 920 guests
Please welcome our newest community members: