cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
oslosa Frequent Visitor
Frequent Visitor

Sumtotal Currency Conversion Problem

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]))
1 ACCEPTED SOLUTION

Accepted Solutions
oslosa Frequent Visitor
Frequent Visitor

Re: Sumtotal Currency Conversion Problem

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.

View solution in original post

5 REPLIES 5
Super User
Super User

Re: Sumtotal Currency Conversion Problem

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  Datanaut

oslosa Frequent Visitor
Frequent Visitor

Re: Sumtotal Currency Conversion Problem

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.

 

Highlighted
Super User
Super User

Re: Sumtotal Currency Conversion Problem

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  Datanaut

  

oslosa Frequent Visitor
Frequent Visitor

Re: Sumtotal Currency Conversion Problem

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.

PBI1.PNGPBI2.PNGPBI3.PNG

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])))
oslosa Frequent Visitor
Frequent Visitor

Re: Sumtotal Currency Conversion Problem

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.

View solution in original post

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 341 members 3,373 guests
Please welcome our newest community members: