Power BI Incorrect Total in Pivot Table

[TanHY]

Hey Power BI Wizard, 

I have met the total error in my power bi, and I scratched out my head still couldnt solve it. For context: I have a forecast and actual sales dataset for my power bi, and I wish to dynamically calculated the leftover target to achieve in power bi pivot table. 

Here is my dax:

Development =

VAR _MinDateQuarter = CALCULATE(

    MIN('Date'[Date]),

    REMOVEFILTERS('Date'[Month]),

    'Date'[Fiscal Year Quarter Number] = MIN('Date'[Fiscal Year Quarter Number]),

    ALLEXCEPT('Date','Date'[Fiscal Year], 'Date'[Fiscal Year Quarter] )

)

VAR _MaxDateQuarter = CALCULATE(

    MAX('Date'[Date]),

    REMOVEFILTERS('Date'[Month]),

    'Date'[Fiscal Year Quarter Number] = MIN('Date'[Fiscal Year Quarter Number]),

    ALLEXCEPT('Date','Date'[Fiscal Year], 'Date'[Fiscal Year Quarter] )

)

VAR _ActualMaxDate = EOMONTH(TODAY(), -1) -- Return the last date of last month

VAR _ForecastMinDate = EOMONTH(TODAY(), -1) + 1 -- Return the first date of this month

VAR _MonthActualValue =  CALCULATE(SUM(SG_Budget_Forecast_View[Value]))

VAR _TotalActualValue = CALCULATE(SUM(SG_Budget_Forecast_View[Value]), DATESBETWEEN('Date'[Date],_MinDateQuarter,_ActualMaxDate))

VAR _TotalForecastValue = CALCULATE(SUM(Forecast[Value]), DATESBETWEEN('Date'[Date], _MinDateQuarter, _MaxDateQuarter))

VAR _MonthActualCount = CALCULATE(

        COUNT(SG_Budget_Forecast_View[Value]),

        ALLEXCEPT('Date', 'Date'[Fiscal Year], 'Date'[Fiscal Year Quarter]), -- Retains only the fiscal year and quarter filters

        SG_Budget_Forecast_View[NormalDate] < _ActualMaxDate -- Filters rows by date

    )

VAR _MonthForecastCount = CALCULATE(

        COUNT(Forecast[Value]),

        ALLEXCEPT('Date', 'Date'[Fiscal Year], 'Date'[Fiscal Year Quarter]), -- Retains only the fiscal year and quarter filters

        Forecast[NormalDate] >= _ForecastMinDate -- Filters rows by date

    )

VAR _CurrentMaxQuarterDate = SWITCH(

    TRUE(),

    MONTH(_ForecastMinDate) IN {8,11,2,5}, EOMONTH(_ForecastMinDate,2),

    MONTH(_ForecastMinDate) IN {9,12,3,6}, EOMONTH(_ForecastMinDate,1),

    EOMONTH(_ForecastMinDate,0)

)

RETURN

SWITCH(

    TRUE(),

    MAX('Date'[Date]) <= _ActualMaxDate, _MonthActualValue,

    AND(

        MAX('Date'[Date]) >= _ActualMaxDate + 1,

        MAX('Date'[Date]) <= _CurrentMaxQuarterDate

    ), ((_TotalForecastValue - _TotalActualValue) / _MonthForecastCount) ,

    SUMX(Forecast,_TotalForecastValue / _MonthForecastCount)

    )

I know there is something related to iteration row. Hope to get some advices and lets discuss.

[danextian · Accepted solution]

Hi @TanHY 

The most likely cuplrit is you conditional formula after  RETURN. MAX ('Date'[Date]) is evaluated differently at each hierarchy level. Try in a new measure:

SUMX (
    SUMMARIZECOLUMNS (
        'table'[Category],
        'table'[Subcategory],
        "@Development", [Development ]
    ),
    [@Development]
)

Change category and subcategory to the actual row columns in your matrix.

[danextian · Accepted solution]

Hi @TanHY 

The most likely cuplrit is you conditional formula after  RETURN. MAX ('Date'[Date]) is evaluated differently at each hierarchy level. Try in a new measure:

SUMX (
    SUMMARIZECOLUMNS (
        'table'[Category],
        'table'[Subcategory],
        "@Development", [Development ]
    ),
    [@Development]
)

Change category and subcategory to the actual row columns in your matrix.

[TanHY · Accepted solution]

Hi, 

Thanks. its work!!

[danextian]

Please accept my post as the solution. Thanks!