Forum Discussion

TanHY's avatar
TanHY
Helper I
1 year ago
Solved

Power BI Incorrect Total in Pivot Table

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.
Need Help
  • danextian's avatar
    danextian
    1 year ago

    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.

3 Replies

  • danextian's avatar
    danextian
    Super User
    1 year ago

    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's avatar
      TanHY
      Helper I
      1 year ago

      Hi, 

      Thanks. its work!!