Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Incorrect Column subtotals in matrix visual

Hi All,   I've been trying to get this to work but I can't seem to wrap my head around how to formulate the measure using sumx.   This is my current visual: Columns are period numbers pull...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    I never said that the execution was simple.

    Anyway, I took your advice and did combine the actuals & scenario tables (in query, not through union) to get one big facts table and adjusted my initial measure.

    Rolling Scenario =
    var _HighestPeriod = CALCULATE(MAX(Data[Period]), Data[Scenario] = "ACT", 'Time'[Relative Year] = 0)
    var _CurrentPeriod = SELECTEDVALUE('Time'[Period num])
    var _TotalActual = CALCULATE([Actuals CY], 'Time'[Period num] <= _HighestPeriod)
    var _TotalScenario = CALCULATE([Scenario M], 'Time'[Period num] > _HighestPeriod)

    RETURN IF(ISINSCOPE('Time'[Period num]),
        IF(_CurrentPeriod <= _HighestPeriod, [Actuals CY], [Scenario M]),
        _TotalActual + _TotalScenario)
     
    Using this, i get the view i expect and want.
     
    Topic can be marked as solved and closed.
Anonymous's avatar
Anonymous
Not applicable
1 year ago

Thanks for the replies from lbendlin.

 

Hi Anonymous ,

 

Please try the following DAX formula:

Rolling  v2 = 
var _scenario = SELECTEDVALUE('Scenario Selection'[Scenario])
return
SUMX(VALUES('Time'[Period num]),
 IF('Time'[Period num] <= CALCULATE(MAX('Data Actuals'[Period]), 'Time'[Relative Year] = 0), [Sum Actuals CY], 
 SWITCH(TRUE(),
    _scenario = "Budget", -CALCULATE(SUM('Data FC-BU'[Value]), 'Data FC-BU'[Scenario] = _scenario),
    _scenario = "FC2", -CALCULATE(SUM('Data FC-BU'[Value]), 'Data FC-BU'[Scenario] = _scenario),
    _scenario = "FC4", -CALCULATE(SUM('Data FC-BU'[Value]), 'Data FC-BU'[Scenario] = _scenario),
    _scenario = "LY", [Sum Actuals LY])))

 

Result:

 

Best Regards,
Zhu

 

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.