Forum Discussion
cumulative sum issues when filtering
My measure will calculate cumulative totals for all attributes that you put into the rows (or columns), EXCEPT the fields that are included in the ALLSELECTED (Date and AttritionFlag), and ID, as this is what the measure counts.
Please check out this file where I've added some data for "Gender" and "OtherAttribute" with some sample data: https://www.dropbox.com/s/xltrrrpedty57jm/CumulativeSumWithFilters.pbix?dl=0
Please note that I don't use the VALUES like you do.
The file also contains a version with a Dimension table for AttritionFlag. If you use the field from that table instead, you won't have blanks in your cumulative figures where there is no value in the detail table (Table1).
ImkeF Perfect, just getting my head around. I tried using crossjoin between date and gender in your calculation but it didn't work. I then tried replacing date with gender in crossjoin in your calculation and seems working. So is it that to get the right context for the running total we need a filter table and as we need two parameter for cross join, any field from the table will do the trick as long as one of the field is attrition flag as running total need to be across the attrition flag? Please enlighten me or point me to the right blog:)
I also tried one of the calculation earlier in the thread and seems to be working.
Cumulative Pledges 4 =
CALCULATE (
[Pledges],
FILTER (ALLSELECTED(DimAttrition[Attrition Flag]),
DimAttrition[Attrition Flag] <= MAX ( DimAttrition[Attrition Flag])
)
)
May be i need a break, sure doing something wrong here...
Thanks for your help
Your last measure works because it references the separate Dimension-Table “DimAttrition” This hasn’t been used earlier in this thread. This measure will also work for the date-filter if you run it from a separate calendar table, so you could omit the crossjoin then.
Please find some explanations about the tricky behaviour of ALLSELECTED here: https://www.sqlbi.com/articles/understanding-allselected/