Forum Discussion

Metricbits's avatar
Metricbits
Frequent Visitor
5 years ago
Solved

Measure with switch - incorrect totals

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

Need Help
  • Metricbits's avatar
    Metricbits
    5 years ago

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

2 Replies

  • Metricbits's avatar
    Metricbits
    Frequent Visitor
    5 years ago

    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's avatar
    MFelix
    Icon for Super User rankSuper User
    5 years ago

    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.