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 pulled from time table.

and this is my measure (used for values):

Rolling  Scenario =
var _scenario = SELECTEDVALUE('Scenario Selection'[Scenario])
return
 IF(SELECTEDVALUE('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]))
 
The idea is quite simple: if the period number in the columns is lower than or equal to the latest available data in my actuals dataset (which spans 2 year, so i'm limiting it to current year with the relative year filter). I want it to show the sum of my actuals.
If the period number is higher, i want it to show scenario (from my FC-BU dataset) based on a slicer selection. 

So far this is working, but the column subtotals are incorrect (as expected within pbi logic) as it's only summing up all period numbers lower than or equal to my max period (so >= 8). 
I've read up on it and know i need to apply sumx logic somewhere, but i cant make it work. Any help would be greatly appreciated.
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.

7 Replies

  • 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

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Test PBI file can be found here:

    https://drive.google.com/file/d/1uP5_yYGaoWqFokOzq7Jdymvh52xf-LlW/view?usp=sharinghttps://drive.google.com/file/d/1uP5_yYGaoWqFokOzq7Jdymvh52xf-LlW/view?usp=drive_link

    My expected outcome would be the following; 

    123456789101112Total
                    90.657                -747                21.505                37.779                63.103                 -22.834                 -22.596                87.824                99.707                97.010                96.908                98.651                 646.968

     

    Individual column results are correct, but total amount is different as it's only taking the sum of P1-P8.

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        Should work now

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