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