Forum Discussion
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
- jahidaImpactful Individual
Can you add a clause to the CALCULATE function with ALL(Table[Column]) with whatever column the slicer is based on?
- AnonymousNot 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!
- MWinter225Advocate IV
Anonymous This helped me a lot thanks!