cumulative sum issues when filtering

[H_Jones]

I am having some issues with my cumulative sum. I initially thought it was working fine, until I added a date slicer onto the page and now when that is used the sum goes bonkers, I dont even understand the numbers being produced.

Here is the measure formula:

Cumulative Pledges = CALCULATE ([Pledges],
FILTER (ALL('Attrition Data'[Attrition Flag]),
'Attrition Data'[Attrition Flag] <= MAX ( 'Attrition Data'[Attrition Flag])
)
)

And here is an example of some data where it works great:

But when I filter down some of the dates involved in the report here is what I get:

So, what is mind-boggeling is that the 'Totals' are correct but the values in the matrix are incorrect, surely this means what I need is possible.

Can anyone explain what is going on and how i can fix this?

EDIT** Here is a text version of the report and an image of the oncorrect data. I am also having issues using the channel slicer. 

https://1drv.ms/u/s!Ah4C_QyiKS7WkEcGk8E3kPhGzZ4s

Please help!

Thanks,

Harriet.

[H_Jones]

Hey @thakks, Thansk, This seems to get me almost there is not quite right but its very close, could you explain a little more what difference this makes so i can try and figure out why it isnt 100% accurate?

For your interest, its just right at the very end now that it start to tail of the actual value:

Thanks for your help!

H

[thakks]

you will need to provide the calculation you did for the cumulative pledges. Can you share the report with the sample data.

[H_Jones]

Sorry for the wait @thakks, I have come to realise that the answer you gave was perfectly right, the next issue i found what is if i want to split this measure by different metrics then i will need to make a new measure with the new metric in the Values() part of the formula, i.e.

for the chart where the ledgend is channel i use:

Cumulative Pledges =
CALCULATE (
[Pledges],
FILTER (ALLSELECTED('DD Attrition Data'[Attrition Flag]),
'DD Attrition Data'[Attrition Flag] <= MAX ( 'DD Attrition Data'[Attrition Flag])
),VALUES('DD Attrition Data'[Channel]
))

but if i want to add a chart for the attrition split by gender i need to use:

Cumulative Pledges = 
CALCULATE (
[Pledges],
FILTER (ALLSELECTED('DD Attrition Data'[Attrition Flag]),
'DD Attrition Data'[Attrition Flag] <= MAX ( 'DD Attrition Data'[Attrition Flag])
),VALUES('DD Attrition Data'[Gender]
))

To get the correct answer. This would be quite an untidy solution but I will make all of the mesures if you think this is the best way to do it?

Thanks,

Harriet.

[ImkeF]

Hi Harriet,

please check this measure:

Cumulative Pledges 2 = 
CALCULATE (
    [Pledges],
    FILTER (CROSSJOIN(ALLSELECTED(Sheet1[Sign up Date]),ALLSELECTED(Sheet1[Attrition Flag])),
				Sheet1[Attrition Flag] <= MAX ( Sheet1[Attrition Flag])
			)
)

It includes the date-field into the selection, so everything you slice and dice there will be reflected in the measure as well.

[thakks]

@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])
)

[ImkeF]

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).

[thakks]

@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

[ImkeF]

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/

[thakks]

@ImkeF thanks for the solution and sharing the knowledge. I definately need more reading around how DAX work 🙂 

[Ashish_Mathur]

Hi,

I do not see a download button there.