Total Incorrect

[Binway]

Hi All,

This code summarises the data correctly and places the values in the Previous Month as required but won't display the correct total.

CALCULATE(
SUMX (
    SUMMARIZE (
        Opportunity,
        Opportunity[Currency],
        'Probability'[Probability Lower Band Value],
        'Currency conversion'[Conversion rate NZ to AUD]
    ),
    VAR TotalAmount =
        CALCULATE ( SUM ( Opportunity[Total amount] ) )
    VAR ConversionRateFinal =
        IF ( Opportunity[Currency] = "AUS$", 1, 'Currency conversion'[Conversion rate NZ to AUD] )
    VAR ProbabilityFinal = 'Probability'[Probability Lower Band Value]/ 100
    RETURN
        TotalAmount * ProbabilityFinal
            / ConversionRateFinal
            / 1000
),PREVIOUSMONTH('Date'[Date]))

Which produces the correct result for each previous month that align to the current month as displayed in this table below but the total is not correct. I have read about HASONEVALUE but couldn't see how to apply it in this case.

Appreciate the time.

[Chihiro · Accepted solution]

Check if your "Month-Year" column has one value, then do your current calculation.

If not do total calculation.

So something like...

Measure =
IF (
    HASONEVALUE ( 'Date'[Month-year] ),
    CALCULATE (
        SUMX (
            SUMMARIZE (
                Opportunity,
                Opportunity[Currency],
                'Probability'[Probability Lower Band Value],
                'Currency conversion'[Conversion rate NZ to AUD]
            ),
            VAR TotalAmount =
                CALCULATE ( SUM ( Opportunity[Total amount] ) )
            VAR ConversionRateFinal =
                IF (
                    Opportunity[Currency] = "AUS$",
                    1,
                    'Currency conversion'[Conversion rate NZ to AUD]
                )
            VAR ProbabilityFinal = 'Probability'[Probability Lower Band Value] / 100
            RETURN
                TotalAmount * ProbabilityFinal
                    / ConversionRateFinal
                    / 1000
        ),
        PREVIOUSMONTH ( 'Date'[Date] )
    ),
    "Your total calculation"
)

Replace "Your total calculation" with actual measure/DAX formula.