Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
Super User

@Anonymous - 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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

2 REPLIES 2
Greg_Deckler
Super User
Super User

@Anonymous - 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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

@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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors