Forum Discussion

H_Jones's avatar
H_Jones
Helper I
8 years ago

cumulative sum issues when filtering

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.

Need Help

30 Replies

  • MFelix's avatar
    MFelix
    Super User
    8 years ago

    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's avatar
      H_Jones
      Helper I
      8 years ago

      It hasnt changed the values at all unfortunately.

      • MFelix's avatar
        MFelix
        Super User
        8 years ago

        Hi H_Jones,

         

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

         

        Regards,

        MFelix

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    8 years ago

    Hi,

     

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

    • H_Jones's avatar
      H_Jones
      Helper I
      8 years ago

      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