SUMX shows incorrect grand total but correct at row level

[danextian]

Hello, 

I am stuck at doing a sumx with this formula. The formula below what it does is compute for the remaining invoice balance as of a given date but the numbers are in transaction currency (USD, NZD) and need to be converted in AUD. The converted to AUD balances of these invoices are then summed up to get the total balance.  In the InvoiceBalance table, there is a column indicating the transaction currency. The Currency, Rate and Month is in a separate table. 

InvoiceBalance - Current (Debtor) tc =
VAR __END_DATE =
    MIN ( TODAY (), [Max Date with Data] )
VAR __START_DATE =
    EOMONTH ( __END_DATE, -1 ) + 1
VAR __INVOICE =
    SELECTEDVALUE ( InvoiceBalance[Invoice ID] )
VAR __MAX_DATE = __END_DATE
VAR __MIN_DATE =
    EOMONTH ( __MAX_DATE, -1 ) + 1
VAR __INV =
    ROUND (
        CALCULATE (
            [TotalInvoiceAmount TC],
            ALL ( Dates ),
            FILTER (
                ALL ( InvoiceBalance[Date] ),
                InvoiceBalance[Date] >= __START_DATE
                    && InvoiceBalance[Date] <= __END_DATE
            ),
            InvoiceBalance[Type] = "ACCREC"
        ),
        2
    )
VAR __DEDUCTIONS =
    ROUND (
        CALCULATE (
            [TotalInvoiceDeductions TC],
            ALL ( Dates ),
            FILTER (
                ALL ( InvoiceBalance[Date] ),
                InvoiceBalance[Date] >= __START_DATE
                    && InvoiceBalance[Date] <= __END_DATE
            ),
            FILTER ( ALL ( InvoiceBalance[Dates] ), InvoiceBalance[Dates] <= __MAX_DATE ),
            InvoiceBalance[Type] = "ACCREC"
        ),
        2
    )
RETURN
    __INV - __DEDUCTIONS

I tried doing a SUMX but the total is unexpected --way overstated. Instead of:

RETURN
    __INV - __DEDUCTIONS

I entered:

RETURN
    SUMX (
        InvoiceBalance,
        IF (
            InvoiceBalance[Currency] = "AUD",
            __INV - __DEDUCTIONS,
            ( __INV - __DEDUCTIONS )
                * CALCULATE (
                    MAX ( FXRate[Rate] ),
                    FILTER (
                        FXRate,
                        FXRate[Month] = MAX ( Dates[Year Month] )
                            && FXRate[Currency] = InvoiceBalance[Currency]
                    )
                )
        )
    )

The result is correct at  invoice level but total seems to show the total of all rows instead of what is the balance multiplied by currency rate.

[parry2k]

@danextian I guess following will do

RETURN
    SUMX (
       VALUE ( InvoiceBalance[InvoiceNumber] ),
        IF (
            InvoiceBalance[Currency] = "AUD",
            __INV - __DEDUCTIONS,
            ( __INV - __DEDUCTIONS )
                * CALCULATE (
                    MAX ( FXRate[Rate] ),
                    FILTER (
                        FXRate,
                        FXRate[Month] = MAX ( Dates[Year Month] )
                            && FXRate[Currency] = InvoiceBalance[Currency]
                    )
                )
        )
    )

[danextian]

Hello @parry2k ,

Thank you for the reply.  

If i use VALUEs ( InvoiceBalance[InvoiceNumber] ), i won't be able to use any other columns from InvoiceBalance table in my IF argument. They won't show up as available columns when entering the formula.

[parry2k]

@danextian that was just a suggestion, wihtout looking at sample dataset, it si very hard to provide a solution.

[danextian]

Hi @parry2k ,

Happy new year. A little late but would like to post that I figured out what went wrong. Instead of wrapping __INV - __DEDUCTIONS variable in a SUMX , I created another measure to  do the SUMX. The first formula ended with RETURN __INV - __DEDUCTIONS then the other formula references the first formula 

   SUMX (
        InvoiceBalance,
        IF (
            InvoiceBalance[Currency] = "AUD",
            [First Formula],
            [First Formula]
                * CALCULATE (
                    MAX ( FXRate[Rate] ),
                    FILTER (
                        FXRate,
                        FXRate[Month] = MAX ( Dates[Year Month] )
                            && FXRate[Currency] = InvoiceBalance[Currency]
                    )
                )
        )
    )