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

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information. Do not include anything that is unrelated to the issue or question.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523