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]))
