Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to Solution.
Can you add a clause to the CALCULATE function with ALL(Table[Column]) with whatever column the slicer is based on?
Can you add a clause to the CALCULATE function with ALL(Table[Column]) with whatever column the slicer is based on?
@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!
@Anonymous This helped me a lot thanks!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |