SumTotal Currency Conversion

[oslosa]

Hi, I am doing a currency conversion with a double lookup that matches on both currency and time. This works fine for the individual rows in the table, however, the sumtotal is wrong. I found that one has to treat sumtotals separately, and that HASONEVALUE() is a good function to ensure this.

The problem is what DAX formula to use for the sumtotal, marked as "?" in the formula below. I have tried several different methods using SUMX(), but I haven't been able to solve it.

Also, I have scoured the web for tutorials and solutions but all currency conversion tutorials seem to have the same issue.

Help would be greatly appreciated!

Note that there is also a filtering here between currency selection "NOK" and "Local Currency".

Curr_adj_value = SWITCH(TRUE();

[Currency selection]="NOK";IF(HASONEVALUE('CapEX Documents'[Budget]);SUM('CapEX Documents'[Budget])*LOOKUPVALUE('Currencies'[NOK per unit];'Currencies'[Currency];MAX('CapEX Documents'[Currency]);'Currencies'[Year];YEAR(MAX('CapEX Documents'[Scheduled start])));"?");

[Currency selection]="Local Currency"; SUM('CapEX Documents'[Budget]))