Total with SUM instead of MAX

[MalyMajo]

I have an issue in my data model with calculation of totals.

My inputs are following:

- burnt effort per resource and day (in hours)

- scheduling report (roster with resource details)

- rate card with different loaded cost rate (LCR) per each activity type and country

- exchange rate for each currency on monthly level

- mapping table of countries and their currencies

I am trying to get the cost calculated in USD, considering that the rates differ every month.

The measure calculates properly on resource level, but not when summing on month level.

I understand, that the reason is, that the measure calculates MAX of the rate and when all the monthly values are summed, only the highest exchange rate is considered. However, I cannot figure out how to change the measure to SUM the calculations on total level instead of calculating it separately.

I have tried to follow the currency conversion example from DAX Patterns (link).

Costs USD:=VAR AggregatedCostsInCurrency =
	ADDCOLUMNS(
		SUMMARIZE(
			Actuals,
			'Calendar'[Year_Month],
			Actuals[Name]
		),
		"@CostsAmountInCurrency",[Costs (internal)],
		"@Rate", CALCULATE(
			MAX(ExchangeRates[Rate]))	
			)
VAR CostUSD =
	SUMX (
		AggregatedCostsInCurrency,
		DIVIDE([@CostsAmountInCurrency],[@Rate])
	)
RETURN
	CostUSD

 Here is the diagram view of my simplified data model:

Any help will be appreciated.

[lbendlin]

Most likely you will need to use PRODUCTX  to account for the rate changes.

[MalyMajo · Accepted solution]

PRODUCTX kept giving me same results. However, I have reworked all the measures and it seems to work now.