Forum Discussion
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.
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 
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.
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
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])))