Divide 2 columns from different tables in a Many to Many relationship

[lvallinoto]

Hello,

I have a scenario where I have to divide 2 column, one in one table and the other in another table, which are linked by a Many-to-Many Relationship, and I was hopping to get some help from you Dax geniuses out there, please.

Quick explanation of the scenario : I have a transactions table, with sales, and these sales are in many different currencies. To convert them to a single currency, we have to divide the sales by the rate, which is in a second table rates.

There are 2 types of rates : an average rate per month, and an average rate per year. The link between the 2 tables is the period, and the currency. The relationship between the 2 tables is many to many ( Check model , please )

What I have tried so far: I could make it work, in 2 different ways, but none completely satisfying :

1 ) Dax expression with SumX :

Dividing the sum of sales by the sum of rates ( I must always have one selected rate type, so it's ok to sum the rates )

CalculatedValue = Sum( 'Transactions'[Sales] ) /Sum( 'Rates'[RateValue] )

Then applying a sumx on the Rate table :

SalesRateType = SUMX(VALUES('Transactions'[KeyColumn]), [CalculatedValue])

This works, but the performance is terrible. I have a transactions table with millions of records. Takes almost 1 minute to calculate YTD.

2 ) Forcing a many to 1 relationship :

If I apply some modeling to the tables, I can make it a 1 to Many relationship and create a calculated column, which will give a good performance. But that involves joining both tables, and it takes ages, even with Chris Webb's ideas, it will still take more than 1 hour to join the tables.

What I would like to know: Is there any other way of doing this? I'd really appreciate some ideas that I can test.

Simplified Model:

Sales:

Period	Currency	Sales	KeyColumn
202001	USD	55	202001USD
202001	EUR	13	202001EUR
202001	GBP	7	202001GBP
201904	USD	65	201904USD
201904	EUR	27	201904EUR
201904	GBP	96	201904GBP
202001	GBP	33	202001GBP
202001	EUR	44	202001EUR

Rates:

RateType	KeyColumn	RateValue
AvgMonthRate	202001USD	55
AvgMonthRate	202001EUR	13
AvgMonthRate	202001GBP	7
AvgMonthRate	201904USD	65
AvgMonthRate	201904EUR	27
AvgMonthRate	201904GBP	96
AvgAnnualRate	201904USD	55
AvgAnnualRate	201904EUR	13
AvgAnnualRate	201904GBP	7
AvgAnnualRate	202001USD	65
AvgAnnualRate	202001EUR	27
AvgAnnualRate	202001GBP	96

Expected Results:

Thanks in advance!

[lbendlin]

What if you keep your annual rates and your monthly rates in different tables?

[lvallinoto]

hi @BIBen 

I have tried that approach as well. But then you need a conditional measure and an extra table with a single column to calculate sales. Sometimes they add / remove rate types, so I'd like to keep it dynamic if possible.

I have good experience with data modeling, but I'm relatively new to power BI, so I'm just checking if there's some smart way of sorting this I'm not aware of.

If I'm doing the right things and it is what it is, I'll mark your answer as correct, thanks