Running total/ cumulative sum measure not responding to some slicers

Anonymous — Wed, 29 Jul 2020 14:57:31 GMT

Hi all,

I’m pretty new to Power BI and DAX and I’m having some strange behaviour with some measures that I’ve calculated. It will take a bit of space to explain this, but I’d *really* appreciate any help as I’ve been racking my brains over this for days and I can’t solve it.

I have a data table that contains member information for a card scheme. I have a few measures that I’m interested in that need to be created, the main ones being:

The number of new members per quarter
The running count/cumulative total in each quarter

My problem is that my running count measure responds weirdly to slicers – it will recalculate as it should for some slicers, but not for others. I think it might be something to do with the relationships between data tables, but not sure.

Here is the code for my measures:

1) New members:

N of Members = COUNT('Members'[MembershipNumber])

2) Running count

This is where things get a bit complicated -- although I’m calculating the running total by quarter, the data type for 'quarter' can’t be set as a date because the column is a text string that concatenates year + quarter, which allows me to plot data for all quarters over multiple years. Instead, I’ve created an index for the quarters that orders them (e.g. Q1-2019 has an index of 1, Q2-2019 has an index of 2, etc.) and used that index to calculate my running total.

So I have a column ‘Year-Qtr’ in my members data, and then a column for the index ‘Qtr_index_no’ in another data table that gives the index for each quarter. Then I’ve used this code to calculate the running total:

Running Count = VAR MaxIndex = MAX( 'TargetMemberships'[Qtr_index_no] ) RETURN CALCULATE( COUNT('Members'[MembershipNumber]), 'TargetMemberships'[Qtr_index_no] <= MaxIndex, ALL('Members')

This works and this is my output with the two measures:

The problem is with the running count when I want to apply slicers. I have 3 slicers that I want to apply (postcode, gender, and deprivation decile). Gender is a column from the same data table as the members data, but Postcode and Deprivation Decile are columns from different data tables.

At the moment the running count recalculates as it should when I change the postcode:

But if I want to filter based on Gender or Deprivation Decile, it doesn’t recalculate, but just shows the grand running totals as if no filters were applied:

I think it responds to the postcode slicer because of the way the relationships are set up, but I don’t know why. The postcode data are in a data table that isn’t directly linked to the Members data, whereas the others are directly linked. The relationships look like this:

Does anybody know why this is happening and what I can do to make it so that the running total recalculates correctly for all slicers?

Apologies for the length of the post, but I wanted to try to explain the problem as clearly as possible! I'd massively appreciate any help! Thanks very much 🙂

Re: Running total/ cumulative sum measure not responding to some slicers

Greg_Deckler — Wed, 29 Jul 2020 15:00:14 GMT

Anonymous - It probably has to do with your ALL statement. Try replacing that with an ALLEXCEPT that respects your slicer columns.

Re: Running total/ cumulative sum measure not responding to some slicers

Anonymous — Wed, 29 Jul 2020 15:09:42 GMT

Greg_Deckler Oh my goodness, I can't believe it was that simple. I've spent hours looking at this. I find DAX commands quite unintuitive so never really know what to try. It works now, thank you so much that's made my day!

topic Re: Running total/ cumulative sum measure not responding to some slicers in DAX Commands and Tips

Running total/ cumulative sum measure not responding to some slicers

Re: Running total/ cumulative sum measure not responding to some slicers

Re: Running total/ cumulative sum measure not responding to some slicers