Total with SUM instead of MAX

MalyMajo — Thu, 03 Feb 2022 17:21:52 GMT

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.

Re: Total with SUM instead of MAX

lbendlin — Fri, 04 Feb 2022 14:37:55 GMT

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

Re: Total with SUM instead of MAX

MalyMajo — Mon, 14 Feb 2022 11:29:33 GMT

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

topic Re: Total with SUM instead of MAX in DAX Commands and Tips

Total with SUM instead of MAX

Re: Total with SUM instead of MAX

Re: Total with SUM instead of MAX