Cumulative measure missing a row in its count.

[BarnyQuack]

Hi,

I have an issue with a cumulative not working when I have no filters selected:

The cumulative should start at 4 for 'Today', which is

'Count of Status = Y' + count rows which have bucket = 2,

i.e. 2+2 = 4.  Instead it is 3.

It looks like this is due to rows for Name = B, there is no 'Today', which it is expecting somehow.

If I do add in that row as a dummy, then it works fine.

 

If I add in a 'Today' row for B, then the cumulative works:

 Here's the DAX for cumulative:

Cumulative for Status and bucket =

VAR CurrentSort = MAX( 'Table'[Sort Code] )

RETURN

CALCULATE(

    [Count rows of Status and buckets],

    FILTER(

        ALLSELECTED( 'Table' ),

        'Table'[Sort Code] <= CurrentSort

    )

)

Where 

Count rows of Status and buckets =

VAR AllCount = COUNTROWS('Table')

VAR CurrentlyY =

CALCULATE(

            COUNTROWS('Table'),

            FILTER(

            ALLEXCEPT('Table','Table'[Name]),

           'Table'[Status] = "Y" ))

RETURN

  AllCount

+

  CurrentlyY

Here's the csv for the table:

Bucket,Sort Code,Name,Status,Count Rows
Today,1,A,,2
Today,1,B,,1
Current Month,2,A,,1
Current Month,2,B,,2
,,A,X,2
,,B,X,1
,,A,Y,1
,,B,Y,1

Many thanks for looking!

[DataNinja777 · Accepted solution]

Hi @BarnyQuack ,

The issue is that your cumulative measure depends on rows existing for each combination of bucket and name. When there’s no row for a specific name in a bucket—like when “B” doesn’t appear in the “Today” bucket—Power BI doesn’t fill in that gap, and your cumulative measure undercounts. Specifically, your measure uses ALLSELECTED('Table'), but if there's no data point for that combination, it silently disappears from the visual, and the cumulative logic skips it.

Your Count rows of Status and buckets measure adds [AllCount] + [CurrentlyY], but [AllCount] only counts visible rows, and when a name has no row in a bucket (like “Today” for B), it doesn’t get counted at all. The cumulative DAX:

Cumulative for Status and bucket =
VAR CurrentSort = MAX('Table'[Sort Code])
RETURN
CALCULATE(
    [Count rows of Status and buckets],
    FILTER(
        ALLSELECTED('Table'),
        'Table'[Sort Code] <= CurrentSort
    )
)

only includes rows that are physically present. So when you're not slicing by Name, and one name is missing for a bucket, Power BI doesn’t create that row, and the count is off. That’s why adding a dummy “Today” row for B fixes it—because now there’s a data point to anchor the cumulative calc for that name.

To fix it properly without dummy rows, you’d need to restructure your model to include all combinations using a disconnected scaffold table of [Bucket] x [Name]. Then, join that to your fact table and use that scaffold in your visuals. That way, every combo exists, and your DAX can properly accumulate over time. Otherwise, it’s like trying to total sales per store when some stores don’t report that day—your total looks wrong just because silence isn’t zero, it’s just missing.

Best regards,

[BarnyQuack]

Hi @DataNinja777 

That worked a treat! Really appreciate that - you're a star!  

It inititially didn't work as it was bad data.

[BarnyQuack]

Many thanks for that @DataNinja777 

That worked - up to a point! There's something I've missed out on.

As you said, I set up a scaffold table with all the various values, then I joined it by Sort Code.   I amended the DAX in the Cumulative to use the 'Bucket Values' [Sort Code].  'Count Rows of Status and buckets' is showing 2s for Next Month etc, when there aren't any: 

 

 

[BarnyQuack]

Even weirder is the cumulative dies at 4 months

all the others work ok.