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
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.