Not showing the Correct Subtotals

LarsAustin — Thu, 11 May 2023 03:15:33 GMT

Hi,

I hope you can help me with this issue. The problems may look pretty simple but I just cannot make it work.

I wrote this DAX code:

VAR _Company = IF(HASONEVALUE(Inventory[Company]), VALUES(Inventory[Company]))
VAR _ValueInAUD = SUMX(VALUES('Item'[Item No.]),[Inventory To Date ($)] * [AU FX Rate])

VAR _Result = IF(_Company = "AU" , [Inventory To Date ($)],_ValueInAUD)

RETURN

_Result

The problem is that on an item level, I am getting the correct result. But the total on Inventory To Date ($ AUD) is not correct. $292,404 + $32,215 = $324,719, not $303,812.

I think $303,812 is result of $292,404 multiplied by the AU FX rate (0.9285) plus $32,315. But I don't like to convert $292,404 since this is already in AUD (the company is AU).

I've tried a modifications in my code but i just cannot get to my desired output.

Appreciate your help in advance.

Thanks

Jojemar

Re: Not showing the Correct Subtotals

johnt75 — Thu, 11 May 2023 08:50:28 GMT

Try

My Measure = SUMX ( VALUES ( Inventory[Company] ), VAR _ValueInAUD = SUMX ( VALUES ( 'Item'[Item No.] ), [Inventory To Date ($)] * [AU FX Rate] ) VAR _Result = IF ( Inventory[Company] = "AU", [Inventory To Date ($)], _ValueInAUD ) RETURN _Result )

Re: Not showing the Correct Subtotals

LarsAustin — Thu, 11 May 2023 22:08:36 GMT

Thank you so much johnt75 ..

Your formula works perfectly.

topic Re: Not showing the Correct Subtotals in DAX Commands and Tips

Not showing the Correct Subtotals

Re: Not showing the Correct Subtotals

Re: Not showing the Correct Subtotals