Incorrect Column subtotals in matrix visual

[Anonymous]

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.

[Anonymous]

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.

[lbendlin]

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 · Accepted solution]

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]

Test PBI file can be found here:

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

My expected outcome would be the following; 

1	2	3	4	5	6	7	8	9	10	11	12	Total
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.

[lbendlin]

link requires access

[Anonymous]

Should work now

[lbendlin]

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

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