cancel
Showing results for
Did you mean:
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

 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%

1 ACCEPTED SOLUTION

Accepted Solutions
Established Member

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

Try this column formula

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

Thanks

2 REPLIES 2
Established Member

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

Try this column formula

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

Thanks

Highlighted
Established Member

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

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

Hope this helps

Thanks

Announcements

Power BI Super User, Greg Deckler, explains

#### Back to School Contest

Engage and empower students with Power BI!

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Ideas
Top Kudoed Authors
Users Online
Currently online: 40 members 920 guests
Recent signins: