Blank totals when using SWITCH, What-if parameters & virtual tables

[mo_jo_jo]

Hello

I have run out of ideas and hope someone can suggest something else to try. 

I have set up 12 what-if parameters, one for each month of the year, which allow me to change the billing hours values for a column. The parameters use the measure [Prediction - FTE - Jan Value] within the formula.

I have set up a SWITCH statement, which works at the month level. But at the total level, no matter what I try, the total does not show any values. DAX code shown below.

FTE Change - External Hours (MSc) = // MSc - Monthly Scenarios. Year = 2020
VAR CurrentSelection = SELECTEDVALUE ( 'Date'[Fiscal Month Year] )
VAR _ForecastFTE = CALCULATE(
    AVERAGE( 'Budget - BoY Forecast KPIs'[Value] ),
    'Budget - BoY Forecast KPIs'[Sub-Category] = "Number of employees, FTE"
)
VAR _BudgetedWorkedHrsIncOvertime =
    CALCULATE (
        SUM ( 'Budget - BoY Forecast KPIs'[Value] ),
        'Budget - BoY Forecast KPIs'[Sub-Category] = "Worked hours incl overtime"
    )
VAR _BudgetedInternalHours =
    CALCULATE (
        SUM ( 'Budget - BoY Forecast KPIs'[Value] ),
        'Budget - BoY Forecast KPIs'[Sub-Category] = "Internal hours"
    )
VAR _Budgeted_HrsforOneFTEmployeeinPeriod = DIVIDE( _BudgetedWorkedHrsIncOvertime, _ForecastFTE )
VAR _SwitchLogic = SWITCH (
        TRUE(),
        CurrentSelection = "Jan 2020", (( _ForecastFTE + [Prediction - FTE - Jan Value] ) * _Budgeted_HrsforOneFTEmployeeinPeriod) - _BudgetedInternalHours,
        CurrentSelection = "Feb 2020", (( _ForecastFTE + [Prediction - FTE - Feb Value] ) * _Budgeted_HrsforOneFTEmployeeinPeriod) - _BudgetedInternalHours ,
        CurrentSelection = "Mar 2020", (( _ForecastFTE + [Prediction - FTE - Mar Value] ) * _Budgeted_HrsforOneFTEmployeeinPeriod) - _BudgetedInternalHours ,
        CurrentSelection = "Apr 2020", (( _ForecastFTE + [Prediction - FTE - Apr Value] ) * _Budgeted_HrsforOneFTEmployeeinPeriod) - _BudgetedInternalHours ,
        CurrentSelection = "May 2020", (( _ForecastFTE + [Prediction - FTE- May Value] ) * _Budgeted_HrsforOneFTEmployeeinPeriod) - _BudgetedInternalHours ,
        CurrentSelection = "Jun 2020", (( _ForecastFTE + [Prediction - FTE - Jun Value] ) * _Budgeted_HrsforOneFTEmployeeinPeriod) - _BudgetedInternalHours ,
        CurrentSelection = "Jul 2020", (( _ForecastFTE + [Prediction - FTE - Jul Value] ) * _Budgeted_HrsforOneFTEmployeeinPeriod) - _BudgetedInternalHours ,
        CurrentSelection = "Aug 2020", (( _ForecastFTE + [Prediction - FTE - Aug Value] ) * _Budgeted_HrsforOneFTEmployeeinPeriod) - _BudgetedInternalHours ,
        CurrentSelection = "Sep 2020", (( _ForecastFTE + [Prediction - FTE - Sep Value] ) * _Budgeted_HrsforOneFTEmployeeinPeriod) - _BudgetedInternalHours ,
        CurrentSelection = "Oct 2020", (( _ForecastFTE + [Prediction - FTE - Oct Value] ) * _Budgeted_HrsforOneFTEmployeeinPeriod) - _BudgetedInternalHours ,
        CurrentSelection = "Nov 2020", (( _ForecastFTE + [Prediction - FTE - Nov Value] ) * _Budgeted_HrsforOneFTEmployeeinPeriod) - _BudgetedInternalHours ,
        CurrentSelection = "Dec 2020", (( _ForecastFTE + [Prediction - FTE - Dec Value] ) * _Budgeted_HrsforOneFTEmployeeinPeriod) - _BudgetedInternalHours 
    )
VAR _TotalLevel = ADDCOLUMNS( SUMMARIZE( 'Date', 'Date'[Fiscal Month Year] ), "Month Totals", _SwitchLogic )

RETURN IF(
    HASONEVALUE( 'Date'[Fiscal Month Year] ),
    _SwitchLogic,
    SUMX( _TotalLevel, [Month Totals] )
)

On the surface, I think the formula at the total level should be doing something "easy" and iterating through each month of the virtual SUMMARIZE table and associating the monthly SWITCH result, but clearly that's not happening for whatever reason 🙁
Can someone kindly suggest what the DAX principle I am missing might be and how I might be able to fix this?

Much appreciated.

Switch formulaBlank total row

[Greg_Deckler]

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

[amitchandak]

Typically You need to summarize at a particular level.  for such calculation

sumx(summarize(table, table[Month], "_sum",[measure]),[_sum])

[mo_jo_jo]

Hi amitchandak

I think that's what I have tried to achieve, but the last part ( [_sum] in your example) is what is not working.

[littlemojopuppy]

This seems overly complicated.  Is it possible to restructure the data model so you don't have to create 12 different parameters and have quite so many conditions in the SWITCH statement?  Possibly including the number of billable hours in the date table by day?  Or possibly another table with billable hours by month with a relationship to the date table?

[mo_jo_jo]

Hej littlemojopuppy

Thanks for the response.
The aim of the task was to allow users to amend the FTE rate as they wish, before that is multiplied to the other hours.
This was my first such exercise with Power BI, so possibly it could have been done differently. As yet I have not come across a way that allows users to do that without individual what-if parameters for each month.