Managing a wrong total result in a visual

[eduardoa]

Hello,

In the table, we have multiple currencies with corresponding rates.

The total result should simply be the multiplication of [Amount] * [LastRate].

While the total displayed in Excel is correct, Power BI is returning an incorrect result.

Could you please review the attached results from both Excel and Power BI? Any assistance would be greatly appreciated!

[Rupak_bi]

Hi @eduardoa ,

The simplest way to achieve this is to create a calculated column and then take sum in the matrix. To do it in DAX use below sintex.

Corrected total =

Var tot_amt = calculate(sum( amount), all(table))

Return 

Sumx(table, amount*last rate/tot_amt)*tot_amt

This will work. If fails, Let me know. I will create table with the image you shared and explain.

[eduardoa]

Hello @Rupak_bi ,

Thanks so much. Will try and let you know. 

[eduardoa]

Hello @Rupak_bi 
Unfortunately, it didn’t work.
The calculated column returned no values

[Rupak_bi · Accepted solution]

Hi Here is your result

Hope images are self explainatory. 

[eduardoa]

Thanks so much @Rupak_bi 

Cheers!

[lukiz84]

Use SUMX

SUMX(YourTable, YourTable[Amount]*YourTable[LastRate])

[eduardoa]

Thanks but already tried with the same wrong results