Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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!
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
Hi @Smalfly ,
Unfortunately the solution in this artikel is for one table. I am using as mentioned 3 different tables.
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.
No probleem, what do you mean bij combine information? Which information exactly?
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.
User | Count |
---|---|
22 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
25 | |
13 | |
11 | |
9 | |
6 |