Measure with switch - incorrect totals

Question

Hi,&nbsp;I have the following measure:&nbsp;&nbsp;&nbsp;BS Act GC =
SWITCH (
    VALUE ( CALCULATE ( MIN ( BalanceSheet[FxTypeNumber] ), ALL ( 'Calendar' ) ) ),
    1, [BS Act GC MnthEnd],
    2, [BS Act GC Historical]
)&nbsp;&nbsp;&nbsp;where two other measures used in the one above are:&nbsp;&nbsp;BS Act GC MnthEnd =
DIVIDE ( [BS Act LC], [BS Rate EOM] )&nbsp;&nbsp;&nbsp;and&nbsp;&nbsp;&nbsp;BS Act GC Historical =
CALCULATE (
    SUMX (
        BalanceSheet,
        DIVIDE (
            [BS Act LC],
            IF (
                ISBLANK (
                    LOOKUPVALUE (
                        'Exchange Rates'[Amount],
                        'Exchange Rates'[Month], BalanceSheet[Date],
                        'Exchange Rates'[Currency], BalanceSheet[BaseCurrency],
                        'Exchange Rates'[FX_Type], BalanceSheet[FxType]
                    )
                ),
                1,
                LOOKUPVALUE (
                    'Exchange Rates'[Amount],
                    'Exchange Rates'[Month], BalanceSheet[Date],
                    'Exchange Rates'[Currency], BalanceSheet[BaseCurrency],
                    'Exchange Rates'[FX_Type], BalanceSheet[FxType]
                )
            )
        )
    ),
    FILTER ( ALL ( 'Calendar' ), 'Calendar'[Date] &lt;= MAX ( 'Calendar'[Date] ) )
)&nbsp;&nbsp;&nbsp;However, the total for the top measure (BS Act GC) doesn't add up correctly&nbsp;I normally use summarize to fix similar issues but I can't figure out how to apply it correctly with SWITCH. Please help

metricbits · Accepted Answer

I solved the problem using this measure instead:&nbsp;BS Act GC = 
VAR BalanceSheetVirt =
    ADDCOLUMNS (
        CALCULATETABLE (
            BalanceSheet,
            FILTER ( ALL ( 'Calendar' ), 'Calendar'[Date] &lt;= MAX ( 'Calendar'[Date] ) )
        ),
        "Rate Month End",
            LOOKUPVALUE (
                'Exchange Rates'[Amount],
                'Exchange Rates'[Currency], BalanceSheet[BaseCurrency],
                'Exchange Rates'[Month], MAX ( 'Calendar'[MonthStartDate] ),
                'Exchange Rates'[FX_Type], "Month End"
            ),
        "Rate Historical",
            LOOKUPVALUE (
                'Exchange Rates'[Amount],
                'Exchange Rates'[Currency], BalanceSheet[BaseCurrency],
                'Exchange Rates'[Month], BalanceSheet[Date],
                'Exchange Rates'[FX_Type], "Month End"
            )
    )
RETURN
    SUMX (
        BalanceSheetVirt,
        IF (
            VALUE ( BalanceSheet[FxTypeNumber] ) = 1,
            [Amount_LC] / [Rate Month End],
            [Amount_LC] / [Rate Historical]
        )
    )

Forum Discussion

Related content

Measure Total Incorrect

Measure returning incorrect value

DAX Measure Returning Incorrect Results

Switch measure

Alternative to SWITCH ()?