cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Harriet
Helper II
Helper II

Running total/ cumulative sum measure not responding to some slicers

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:

1.png

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:

2.png

 

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:

3.png

 

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:

4.png

 

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 🙂

1 ACCEPTED SOLUTION
Greg_Deckler
Super User IV
Super User IV

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


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

2 REPLIES 2
Greg_Deckler
Super User IV
Super User IV

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


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

@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!

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Top Solution Authors