Getting the sum of a column when the value is the same as previous row

[CR72022]

Hi guys,

Below you see 3 tables. In the first table there 3 columns from 3 different tables. 

isin column from table dim instrument, hedge from dim trade and PV_hedge is a measure from Fact trade. The total value of PV_hedge is correct namelijk 100 mil.

What I want to achieve is the last table I want to see one row ISIN and the total of PV_hedge. In this case ISIN 2485 and PV_hedge 100 mil.

This is the measure that I am using. I tried to use earlier but it doens not allowd. 

PV_Hedge = 
 CALCULATE(SUM('Fact Trade'[amt_present_value]), 'Dim Trade_oud'[hedge_ref_trade_number] = 'Dim Trade_oud'[hedge_ref_trade_number])

Thank for your help!

[Smalfly]

Hi @CR72022 ,

the group by function might help you out.

Have a look at this page for more information about it:

https://hevodata.com/learn/power-bi-groupby-function/#5

[CR72022]

Hi @Smalfly ,

Unfortunately the solution in this artikel is for one table. I am using as mentioned 3 different tables.

[Smalfly]

You indeed mentioned that. Sorry for not taking it into account.

Since you are using multiple tables, you should first use the addcolumn function to combine the information you need. And once that is done, you can use summarize to calculate the sum.

[CR72022]

No probleem, what do you mean bij combine information? Which information exactly?

[Smalfly]

Somehting like this:

Table = 
var combinetable = ADDCOLUMNS('dim instrument',
"New Hedge",RELATED('dim instrument'[hedge]),
"New PV_hedge",RELATED('Fact Trade'[PV_Hedge]))
return 
SUMMARIZE(combinetable,[ISIN],[New Hedge],Final Hedge,sum(New PV_hedge))

This is just me typing it in here directly to express the idea I had on how to do this. I did not check this code in Power BI so it will definitiely need rework but I hope you get the idea.

As a matter of fact, if the relation between the 3 tables is explicit, you might not even need the ADDCOLUMNS.