Average exchange rate

[JosefinR]

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!

[Anonymous · Accepted solution]

Hi  @JosefinR ,

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*AVExhange

If 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.

[Greg_Deckler]

@JosefinR Use ALLEXCEPT or REMOVEFILTERS.