Not showing the Correct Subtotals

[LarsAustin]

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

[LarsAustin]

Thank you so much @johnt75 ..

Your formula works perfectly.

[johnt75 · Accepted solution]

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
)