Forum Discussion
cumulative sum issues when filtering
ImkeF not sure but your suggested calculation doesn't give me the right running total if i pull in sign up date on the row. The issue seems the field selected on the row that changes the context for the running total. From the image below you can see the top one is through your calculation, second one is pledge total to cross check the value and the bottom on is by changing the calcuation to include signup context along with the channel. Slicer value are pass through correctly. The issue is do we need to create new measure for each of those field on the rows for the running total? If we have like Gender, town etc we will end up having quite a few measure for the same running total in various context!
There is a file H_Jones uploaded in the above thread, if you would liek to have a feel of the data.
Cumulative Pledges = CALCULATE (
[Pledges],
FILTER(ALLSELECTED(Sheet1), Sheet1[Attrition Flag]<= MAX ( Sheet1[Attrition Flag] ) ),
VALUES( Sheet1[Channel]) ,values(Sheet1[Sign up Date])
)
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).
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/
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