Create dynamic calculated matrix - DAX

[nialls_pbi]

Hi all.

I am trying to migrate over from excel based solutions to Power BI.

So far I am good with the M Code in Power Query but I am struggling a touch with the DAX - hoping for some help as don't want to limit myself to only Power Query! I am trying to create a dynamic matrix (cross rates) for some FX Rates. The below table is what I have in excel, where the cells in green are what I start out with. In excel my formaula is, for cell C2, "=ROUND($B2/SUMIF($A:$A,C$1,$B:$B),6)". I can drag this across and down and it works for the rest.

To recreate in PBI I envisage I will have two related tables with a measure of some sort applied across them. I would like it to be dynamic so that if new currencies come in it auotmatically adds them. 2 tables - fact table (columns below) with all currencies and details and fxRate table (rows below) which updates dynamically based on date parameter. Essentially I would like to fill out the below!

Any help would be most appreicated!

Cheers.

Niall.

[v-xiaotang]

Hi @nialls_pbi 

Thanks for reaching out to us.

The fomula for CAD is =ROUND(B2/SUMIF($A:$A,C$1,$B:$B),6), it uses the value of column B (AUD), but how to calculate AUD? 

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[nialls_pbi]

Hi there.

Thanks for reading my post. Apologies for not making it clear!

The cells in green (AUD vs CAD, EUR, CHF, DKK, etc.) are received daily - we have this data. So for the calcs we have 1/0.9128 = 1.09553 (AUDvsAUD/AUDvsCAD = CADvsAUD). Same way AUDvsDKK/AUDvsEUR=EURvsDKK or 4.697/0.6317=7.435492 or B6/B7.

I hope this makes sense.

Thanks again.

Niall.