topic Re: Managing a wrong total result in a visual in DAX Commands and Tips

Managing a wrong total result in a visual

eduardoa — Fri, 08 Nov 2024 08:12:16 GMT

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!

lukiz84 — Fri, 08 Nov 2024 08:16:39 GMT

Use SUMX

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

eduardoa — Fri, 08 Nov 2024 08:32:06 GMT

Thanks but already tried with the same wrong results

Rupak_bi — Fri, 08 Nov 2024 10:43:05 GMT

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 — Fri, 08 Nov 2024 11:12:41 GMT

Thanks so much. Will try and let you know.

eduardoa — Fri, 08 Nov 2024 12:43:54 GMT

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

Rupak_bi — Fri, 08 Nov 2024 13:14:31 GMT

Hi Here is your result

Hope images are self explainatory.

eduardoa — Fri, 08 Nov 2024 14:51:16 GMT

Thanks so much Rupak_bi

Cheers!