Measure with switch - incorrect totals

[Metricbits]

Hi,

I have the following measure:

BS Act GC =
SWITCH (
    VALUE ( CALCULATE ( MIN ( BalanceSheet[FxTypeNumber] ), ALL ( 'Calendar' ) ) ),
    1, [BS Act GC MnthEnd],
    2, [BS Act GC Historical]
)

where two other measures used in the one above are:

BS Act GC MnthEnd =
DIVIDE ( [BS Act LC], [BS Rate EOM] )

and

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] <= MAX ( 'Calendar'[Date] ) )
)

However, the total for the top measure (BS Act GC) doesn't add up correctly

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 solution]

I solved the problem using this measure instead:

BS Act GC = 
VAR BalanceSheetVirt =
    ADDCOLUMNS (
        CALCULATETABLE (
            BalanceSheet,
            FILTER ( ALL ( 'Calendar' ), 'Calendar'[Date] <= 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]
        )
    )

[MFelix]

Hi @Metricbits ,

Have you tried doing the a SUMX of the Class based on isinscope?

Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.