topic Re: Getting the sum of a column when the value is the same as previous row in DAX Commands and Tips

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

CR72022 — Thu, 13 Oct 2022 11:58:29 GMT

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!

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

Smalfly — Thu, 13 Oct 2022 14:45:04 GMT

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

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

CR72022 — Thu, 13 Oct 2022 15:18:01 GMT

Hi Smalfly ,

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

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

Smalfly — Thu, 13 Oct 2022 15:28:08 GMT

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.

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

CR72022 — Thu, 13 Oct 2022 15:33:50 GMT

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

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

Smalfly — Thu, 13 Oct 2022 18:30:45 GMT

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.