Forum Discussion

nialls_pbi's avatar
nialls_pbi
Frequent Visitor
4 years ago

Create dynamic calculated matrix - DAX

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.

Need Help

2 Replies

  • v-xiaotang's avatar
    v-xiaotang
    Community Support
    4 years ago

    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's avatar
      nialls_pbi
      Frequent Visitor
      4 years ago

      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.