Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
10 years ago
Solved

Running Total to Ignore Specific Filter

Okay. So I've made a lot of progress on a Running Total measure (and again much thanks to these forums), but I have one additional question. Bear with me as I'm still learning about contexts and filters and how they impact everything. 

 

My Running Total is adding up the number of graduates over every semester (Spring, Summer, Fall) over multiple years. So for any particular cohort, I can track the total number of graduates as they add up each semester. Works fine.

 

But Summer is often a "lower" enrollment time for a particular cohort, so I want to filter out that Semester for the visualization. The problem when I do this is that the equation no longer includes Summer graduates in my Running Total. I understand why it does this, I just don't know how to prevent this in my equation. 

 

Here's my equation:

 

RunningGradTotal = CALCULATE(DISTINCTCOUNT(DegreeConferred[StudentID]),FILTER(ALLSELECTED(DegreeConferred),DegreeConferred[TERM_END_DATE]<=MAX(TermInfo[TERM_END_DATE])),VALUES(CohortOriginal))

 

Here's an example:

Term      Graduates

12/FA    50

13/SP    100

13/SU    110      //10 students graduated in Summer

13/FA    160      

 

But if I filter out Summer, I get something like:

Here's an example:

Term      Graduates

12/FA    50

13/SP    100

13/FA    150      //I'm missing the 10 students graduated in Summer, I should still get 160 total graduates here regardless of the SU filter

 

 

Any thoughts / help would be greatly appreciated. Thanks!

  • Can you add a clause to the CALCULATE function with ALL(Table[Column]) with whatever column the slicer is based on?

3 Replies

  • jahida's avatar
    jahida
    Impactful Individual

    Can you add a clause to the CALCULATE function with ALL(Table[Column]) with whatever column the slicer is based on?

    • Anonymous's avatar
      Anonymous
      Not applicable

      jahida Thanks for the suggestion, and initially it wouldn't work. But I just modified the original to:

       

      RunningGradTotal2 = CALCULATE(CALCULATE(DISTINCTCOUNT(DegreeConferred[StudentID]),FILTER(ALLSELECTED(DegreeConferred),DegreeConferred[TERM_END_DATE]<=MAX(TermInfo[TERM_END_DATE])),VALUES(CohortOriginal)),ALL(TermInfo[Semester]))

       

      And now that's working. I wasn't aware that you could nest multiple CALCULATE() statements together, but it looks like that makes sense. It the equation ignores the Semester filter I have set at the Page level and still gives me my correct Running Total.

       

      Thanks for the assist!