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.

[Ashish_Mathur]

Hi,

Share the link from where I can download the .pbix file and show the expected result as well.

[H_Jones]

Sorry, I dont know how to make a .pbix file public? I can only share a fake version that i will quickly make of the report as the data is protected.

H

[MFelix]

Hi @H_Jones,

When using the ALL function on your measure what happens is that the calculations are made based on the total table values and overlaps the filter you have in your visuals so the values it gets return is based on the full table and not on the context.

If you want to add a slicer try to change your formula to:

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

Regards,

MFelix

[H_Jones]

It hasnt changed the values at all unfortunately.

[MFelix]

Hi @H_Jones,

Can you please give some data sample to provide a better help.

Regards,

MFelix

[H_Jones]

Here you go:

https://app.powerbi.com/view?r=eyJrIjoiYTUxZjQ3ODAtODg1Zi00Zjg3LTk5NzgtNjk1NmIzODIyM2E5IiwidCI6IjhlY...

Hopefully this works?

H

[MFelix]

Hi @H_Jones,

You are publishing to the wqeb however this don't allow to have a download of the PBIX file can you share it trough Onedrive or Wetransfer, so that can take a copy of the file to make the measure.

Regards,

MFelix

[H_Jones]

@MFelix, @Ashish_Mathur How can I transfer the files to you without specifying email adresses?

H (sorry i was away for a week)

[Ashish_Mathur]

Hi @H_Jones,

Upload the file to OneDrive/Google Docs and share the download link here.

[H_Jones]

@Ashish_Mathur @MFelix How about this:

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

[Ashish_Mathur]

Hi @H_Jones,

I cannot spot any problem there.  When i change the date slicer, the numbers in the Table change.  What is incorrect there?

[H_Jones]

@Ashish_Mathur When you change the date slicer the numbers are no longer cumulative. the build up until about half way then start to decrease again?

H

[Ashish_Mathur]

Hi @H_Jones,

With which specific date settings in teh slicer do you observe that anomaly?

[H_Jones]

Any which arent the full date rage, for example:

[Ashish_Mathur]

Hi,

The problem is happening because of the page leve filter.  If you clear that then the answer is correct.  I tried to incorporate the page leve filter condition in the calculated field formula but with no luck.  I will continur trying.

[H_Jones]

OK, brilliant, thank you @Ashish_Mathur

The value of '100' it just something I picked at random to indicate they have not cancelled, it could be left blank or changed to something else if thats gets us to the correct result.

[Ashish_Mathur]

Hi @H_Jones,

Please check and let me know if changing 100 to blank solves the problem.

[H_Jones]

@Ashish_Mathur Just checked but it doesnt help.

Harriet.

[Ashish_Mathur]

Hi @H_Jones,

Try this measure

=CALCULATE (
    [Pledges],
    FILTER (ALL(Sheet1[Attrition Flag]),SUM(Sheet1[Attrition Flag])<100&&
				Sheet1[Attrition Flag] <= MAX ( Sheet1[Attrition Flag])
			)
)

Hope this helps.

[thakks]

Try this one

=CALCULATE (
[Pledges],
FILTER(ALLSELECTED(Sheet1), Sheet1[Attrition Flag]<= MAX ( Sheet1[Attrition Flag] ) ),
VALUES ( Sheet1[Channel] )
)

Key here is ALLSSELECTED & Values()

ALLSELECTED  will pass the Attrition Flag and Signup Date Filter or any filter selected in to the running total context

VALUES (Channel) will pass the channel context else it will have the running total on Attrition flag, with all having the same value.