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
Highlighted
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
Highlighted
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

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

Back to School Contest

Engage and empower students with Power BI!

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

Top Ideas
Top Kudoed Authors
Users Online
Currently online: 231 members 2,278 guests
Recent signins: