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.
lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
1 year ago

link requires access

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Should work now