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

None of this is remotely simple. If I had to implement this I would include the Actuals in the scenario list like so

 

Scenarios = UNION( 'Scenario Selection' , row("Scenario","Actuals"))
 
and then use that table as a disconnected source for the slicer.
 

 

If you want this cumulative then you would need to materialize this measure across the scenarios and periods (via summarizecolumns), and then sumx that.

Anonymous's avatar
Anonymous
Not applicable
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.