Dual slicer stacked cumulative sum area plot

[sbiedrzycki]

I want to preface this by pointing out that I am still a novice with Power BI, so please feel free to suggest any odd area where something could be improved.

I am struggling with understanding how to model a data set to accommodate slicers that has two different columns that need to be filtered for two different cumulative sums for reporting on stacked area plots.  I have production output that can be classed as many different designs where the specific instance of a design could be production, development, or another kind of manufacturing type.  The goal of the report is to be able to view a stacked area plot of all output with slicers included to allow drilling down to specific designs, specific manufacturing types, or a combination such as all A-design production or B-design development where the output is cumulatively summed over date completed to view volume out over time.

Here is a sample dummy data set.  The table is sparse in that records only appear for the dates completed for the specific kinds out.  Here I calculated the cumulative totals as an example.  The first part I'm struggling with is how to best model this to generate a full table of results for each day for proper visualization for all output on all days.

Design	Type	Number Completed	Date Completed	Cumulative Design Outs	Cumulative Type Outs
A	Production	10	1-Jan-23	10	10
B	Production	10	2-Jan-23	10	20
A	Development	5	3-Jan-23	25	8
A	Production	10	3-Jan-23	25	30
B	Development	3	3-Jan-23	13	8
B	Development	15	4-Jan-23	28	23
B	Development	5	5-Jan-23	33	28
A	Production	5	7-Jan-23	30	35

Here's the DAX I used for the one column.  The other is similar.  This is the second area I'm unsure about.  I don't think I'm filtering appropriately for the slicers to work as intended.  In my attempts so far, I get cases where a selection of a specific design and type slicer gives different results in my cumulative sums despite the fact that both should be identical.

Cumulative Design Outs =

CALCULATE (

    SUM ( Facts[Number Completed] ),

    ALLSELECTED ( Facts ),

    Facts[Date Completed] <= EARLIER ( Facts[Date Completed] ),

    Facts[Type] in ALLSELECTED( Facts[Type] ),

    Facts[Design] = EARLIER (Facts[Design])

)

The following is an example result that I achieved in an early attempt using an explicit cross join in my SQL query in forming the input data set.  The top is by design, and the bottom by manufacturing type (sensitive data removed).  While this worked for the basic case of all data, it did not work with the slicers and I expect there is a cleaner way to evaluate with a good data model in PBI rather than the ugle cross-join.

The following is an attempted model trying to replicate the cross-join within PBI where I hoped to get the slicers working.  Please excuse the various keying columns from testing.  I suspect there is a better approach than what is shown here.  The 1:1 relationship back to the Facts table by the RecordKey appears to introduce undesired artifacts that I don't quite understand.

Here's the main table I setup for reporting: 

Cross Join = SUMMARIZECOLUMNS('Date'[Date], Facts[Design], Facts[Type])

With similar cumulative summation on this table - only an added check to return 0 for blank summation given the sparse facts table.

The result of these cumulative sums isn't correct though.  Despite the table appearing how I would expect.  I think the relationship back to the source facts table is causing the filler date points to be filtered out, but I'm not sure why.

Additionally, when applying the filters, the numbers don't add up despite the plot looking more like it should.  Here you can see the summation by type is correct, but the the summation by design should only include the same 28 output in its tally yet it reports 33 total.   This appears to be including a Production type entry that should have been filtered out as it is not in the selected data set.  This isn't the only discrepancy, all of the points are off.  It's as though the calculation is not taking only the selected, but instead the full data set.

[sbiedrzycki · Accepted solution]

I solved the problem.  I just wanted to report back for future users' sake who rely on these kinds of posts as I do.

The root cause is the misunderstanding of the limits of calculated columns.  The DAX expressions, despite giving the impression of a dynamic aggregation similar to measures, do not appear to support sub-group aggregation with the slicers as I thought.  Measures are required to evaluate in this way.

Creating the following measure allowed implementing the dynamic sub-group aggregation with slicing as desired.

sum_outs =

CALCULATE(SUM('Facts'[Number Completed]),

FILTER(ALL('Date'), 'Date'[Date] <= MAX('Date'[Date])))

[sbiedrzycki]

Anyone else with input on this?  I'm still searching for a solution or at least understanding if what I am trying to do is technically possible within Power BI.  I would appreciate if anyone could link me to any documentation or guides on understanding the fundamentals to how the data filtering layers are intended to work as I suspect a lot of my struggle is that I can't conceptualize how the DAX filter layers and other cross-visual filters are applying to the dataset prior to aggregation.

[sbiedrzycki]

Why is a measure preferred?  I tried this, and it doesn't seem to help in any notable way.  The results are essentially the same.

I tested with this calculated measure with the bare minimum for a cumulative sum (ignoring any of the requirements for the slicer).

Cumulative Type Outs Measure =

VAR total = CALCULATE (

    SUM ( Facts[Number Completed] ),

    FILTER(ALLSELECTED ( 'Cross Join' ),

        'Cross Join'[Date] <= MAX ( 'Cross Join'[Date] ))

)

return IF(ISBLANK(total), 0, total)

The results are still not as expected.

It doesn't retain the accumulated sum for days that are missing from the original sparse data set.  Instead it reports blank resulting in plots like in my first post or as above when I include the ISBLANK check to report zero instead.  Basically, it is returning a cummulative sum of zero on days where there is no record in the base set rather than the result from the prior day.

When I run the same calculation in the column formula, it reports the correct cummulative sum for all days later in the calendar.  The values it plots past the end in the visual are simply wrong.  This appears to be because it is reporting an empty value rather than the actual result.  I'm not sure why though as I'm plotting against the date in this table which exists all the way out to the end of the year.  There is no apparent reason why it should be filtering the dates out of the calculation based on the sparse table.  This is true even if I change from ALLSELECTED to ALL in the filter.

[amitchandak]

@sbiedrzycki , Prefer to create measure in such case

example

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(all('Date'),'Date'[date] <=max('Date'[date])))

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(allselected(date),date[date] <=max(date[Date])))

Cumm Based on Date = CALCULATE([Net], Window(1,ABS,0,REL, ALL('date'[date]),ORDERBY('Date'[date],ASC)))

Cumm Based on Date = CALCULATE([Net], Window(1,ABS,0,REL, ALLSELECTED('date'[date]),ORDERBY('Date'[date],ASC)))

you can add filter function to filter additional data

Running Total/ Cumulative:
https://www.youtube.com/watch?v=h2wsO332LUo&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=42

Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc