Forum Discussion
Average exchange rate
I have a table with an exchange rate that converts the companies' local currency to SEK. The exchange rate is available per day and sales transaction. See table 1 for what that table looks like. I also have a table with budget figures, see table 2. I want to calculate an average value for the exchange rate per month and company and multiply by the budget figures for that month and for that company.
Tabel 1
| Date | YearMonth | CompanyKey | Exchange Rate LCY To SEK |
| 2024-01-02 00:00 | jan-24 | Company A | 1.49622 |
| 2024-01-02 00:00 | jan-24 | Company B | 0.98874 |
| 2024-01-02 00:00 | jan-24 | Company D | 11.1545 |
| 2024-01-02 00:00 | jan-24 | Company C | 1 |
| 2024-01-03 00:00 | jan-24 | Company A | 1.50058 |
| 2024-01-03 00:00 | jan-24 | Company D | 11.1915 |
| 2024-01-04 00:00 | jan-24 | Company A | 1.50027 |
Table 2
| Budget | |||
| Date | SalesRep | Company | Budget |
| 24-jan | 4 | Company D | 1000 |
| 24-feb | 5 | Company B | 2000 |
The budget is made per seller and the transaction where I get the Exchange rate from is per order/sales line.
I've tried the following but can't get it to work because when I bring in salespeople as a hierarchy in a matrix table, it can't find a value for the exchange rate for that salesperson since that salesperson has no sales row. I would like to remove the dependency to everything except company and month and get a fixed value per month and company regardless of whether I want to break it down by salesperson, team or something else.
VAR AVExhange= CALCULATE(AVERAGEX('ExchangeTable', [Exchange Rate LCY To SEK]),USERELATIONSHIP('ExchangeTable'[CompanyKey],Company[CompanyKey]))
VAR BudgetInSEK = CALCULATE('Budget measures'[Budget LCY],USERELATIONSHIP(Budget[CompanyKey],Company[CompanyKey]))
RETURN
BudgetInSEK*AVExhange
Thank you for your help!
Hi Anonymous ,
You can try the following dax:
Measure = var AVExhange= AVERAGEX( FILTER(ALL(ExchangeTable), 'ExchangeTable'[CompanyKey]=MAX('Company'[Company Key])),[Exchange Rate LCY To SEK]) var BudgetInSEK= SUMX( FILTER(ALL('Budget'), 'Budget'[Company Key]=MAX('Company'[Company Key])),'Budget measures'[Budget LCY]) return BudgetInSEK*AVExhangeIf it doesn't meet your desired outcome, can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
Anonymous Use ALLEXCEPT or REMOVEFILTERS.
Hi Anonymous ,
You can try the following dax:
Measure = var AVExhange= AVERAGEX( FILTER(ALL(ExchangeTable), 'ExchangeTable'[CompanyKey]=MAX('Company'[Company Key])),[Exchange Rate LCY To SEK]) var BudgetInSEK= SUMX( FILTER(ALL('Budget'), 'Budget'[Company Key]=MAX('Company'[Company Key])),'Budget measures'[Budget LCY]) return BudgetInSEK*AVExhangeIf it doesn't meet your desired outcome, can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
