Forum Discussion

sbiedrzycki's avatar
sbiedrzycki
Frequent Visitor
3 years ago
Solved

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 ho...
Need Help
  • sbiedrzycki's avatar
    sbiedrzycki
    3 years ago

    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's avatar
sbiedrzycki
Frequent Visitor
3 years ago

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.