Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
sbiedrzycki
Frequent Visitor

Dual slicer stacked cumulative sum area plot

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.

 

DesignTypeNumber CompletedDate CompletedCumulative Design OutsCumulative Type Outs
AProduction101-Jan-231010
BProduction102-Jan-231020
ADevelopment53-Jan-23258
AProduction103-Jan-232530
BDevelopment33-Jan-23138
BDevelopment154-Jan-232823
BDevelopment55-Jan-233328
AProduction57-Jan-233035

 

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.
sbiedrzycki_0-1680890991056.png

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.

sbiedrzycki_1-1680891222705.png

 

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.
sbiedrzycki_2-1680892774355.png

 

sbiedrzycki_3-1680892799067.png

 

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_4-1680892915862.png

 

 

1 ACCEPTED SOLUTION
sbiedrzycki
Frequent Visitor

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_0-1683728074123.png

 

View solution in original post

4 REPLIES 4
sbiedrzycki
Frequent Visitor

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_0-1683728074123.png

 

sbiedrzycki
Frequent Visitor

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
Frequent Visitor

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.
sbiedrzycki_0-1681131381249.png

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
Super User
Super User

@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

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors