Forum Discussion

oslosa's avatar
oslosa
Helper I
6 years ago

SumTotal Currency Conversion

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]))
Need Help
No RepliesBe the first to reply