Sumtotal Currency Conversion Problem

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

[oslosa · Accepted solution]

Solved it!

Curr_adj = IF(HASONEVALUE('Project list'[Cost]);

SUM('Project list'[Cost])*LOOKUPVALUE(Currencies[NOK per unit];Currencies[Currency];MAX('Project list'[Currency]);Currencies[Year];YEAR(MAX('Project list'[Start date])));

SUMX('Project list';CALCULATE(SUM('Project list'[Cost])*LOOKUPVALUE(Currencies[NOK per unit];Currencies[Currency];MAX('Project list'[Currency]);Currencies[Year];YEAR(MAX('Project list'[Start date]))))))

The solution was a combination of SUMX and CALCULATE.

[AlB]

Hi @oslosa 

I don't quite get it.

Is Curr_adj_value a measure?  How exactly are you using it?

Can you clarify this?

This works fine for the individual rows in the table, however, the sumtotal is wrong.

Is table a table visual? Is  sumtotal the total in the visual for the measure? What's in each indivdual row?

Could you share the pbix? 

An example would help

Please mark the question solved when done and consider giving kudos if posts are helpful.

Cheers  

[oslosa]

Hi, yes, Curr_adj_value is a measure. It is connected to two datasets, one is a list wil projects and their cost in local currency, the other is a list of exchange rates for different currencies at different points in time.

The measure is supposed to return the project cost in Local currency when the local currency box is checked and in NOK when the NOK box is checked. When creating a visual, a table, with project names and cost, the measure works perfectly for each individual project in the list, each row. However, the sum total, i.e. the bottom row, returns a bogus number after the conversion to NOK.

The DAX code has to treat this bottom row separately in order for it to work, i.e. that the sumtotal matches the sum of the individual rows/projects, but I need help in writing this part of the DAX code.

[AlB]

Hi @oslosa 

It would certainly help if you showed your tables. I'll give you a generic solution, since I get the idea behind the question but I'm not clear on the details:

Measure =
SUMX ( SUMMARIZE ( Table1, Table1[Col1], Table1[Col2] ), [Your calcs per row] )

 What we're doing here is recreate in the measure what you have in the  table visual. You need all the columns you use in the visual in the summarize. At the total, the summarize will generate a table with all the rows in your visual, and then the sumx will do the calcs per row and sum them all up. At each individual row, the summarize will have only the elements in that row (filter context active). Watch out for context transition when doing the calcs per row.

Please mark the question solved when done and consider giving kudos if posts are helpful.

Cheers  

[oslosa]

Hi, I have created a dummy PBI that is shareable, but I dont see an upload file button here. 

Anyways, here are the visual and two tables the visual gets the data from. The Curr_adj column calculates using the DAX formula below. As you see, the sum total doesnt match the sum of the individual rows in the currency converted column.

Curr_adj = SUM('Project list'[Cost])*LOOKUPVALUE(Currencies[NOK per unit];Currencies[Currency];MAX('Project list'[Currency]);Currencies[Year];YEAR(MAX('Project list'[Start date])))