Cumulative Count DAX

[NessFlood]

I am new Power BI and I need help with a DAX formula.

I would like a forumula that is a cumulative count of "Complete" in the Build Complete FC Status column against the Build Complete FC date columns - thank you 🙂

[Anonymous · Accepted solution]

Hi  @NessFlood ,

You can try the following DAX: Change ALL() to ALLSELECTED(), which can adjust with the changes in the filter.

The ALLSELECTED function gets the context that represents all rows and columns in the query, while keeping explicit filters and contexts other than row and column filters. This function can be used to obtain visual totals in queries.

ALLSELECTED function (DAX) - DAX | Microsoft Learn

CumulativeCompleteCount_Measure =
COUNTX(
    FILTER(ALLSELECTED('CBR_REPORT'),
    'CBR_REPORT'[Build Complete FC Status]="Complete"&&'CBR_REPORT'[BLD110 Build Complete FC]<=MAX('CBR_REPORT'[BLD110 Build Complete FC])),[Ref ID])

If dax gets results that don't match your expected results, can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. We can better understand the problem and help you

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[NessFlood]

thank you - I changed to ALLSELECTED and realised that the visuals need to be drilled down also so I can see the full result 🙂

[Anonymous]

Hi  @NessFlood ,

Are you referring to Meausure in the Total row not 13?You can use IF+ ISINSCOPE() to determine if it is the total row, customize the rule .

Here are the steps you can follow：

1. Create measure.

correct count =
IF(
    ISINSCOPE('CBR_REPORT'[Ref ID]),[CumulativeCompleteCount_Measure],COUNTX(FILTER(ALLSELECTED('CBR_REPORT'),[CumulativeCompleteCount_Measure]<>BLANK()),[Ref ID]))

2. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[NessFlood]

Hi Liu

Your latest formula in purple does calculate in the column but not in the graph. I'm wanting a cumulative graph like the one on the left, which does work with the old formula except when I filter it by region. It should only return 13 completed build sites for Canterbury - pilot but keeps saying 17 and no cumulative line using the new purple formula. Again thanks for any help 😀

[Anonymous]

Thanks for the reply from aduguid , please allow me to provide some information to add:
Hi  @NessFlood ,

According to the error message, you are using the table + column name 'CBR_REPORT' [BLD110 Build Complete FC] in allselect(), followed by 'CBR_REPORT' [Build Complete FC Status] which is not referenced in the dax equation

You can try the following DAX:

Measure：

CumulativeCompleteCount_Measure =
IF(
    MAX([Build Complete FC Status])="Complete",
COUNTX(
    FILTER(ALL('CBR_REPORT'),
    'CBR_REPORT'[Build Complete FC Status]="Complete"&&'CBR_REPORT'[BLD110 Build Complete FC]<=MAX('CBR_REPORT'[BLD110 Build Complete FC])),[Ref ID]))

Caliculated Column：

CumulativeCompleteCount_Column =
IF(
    [Build Complete FC Status]="Complete",
COUNTX(
    FILTER('CBR_REPORT',
    'CBR_REPORT'[Build Complete FC Status]="Complete"&&'CBR_REPORT'[BLD110 Build Complete FC]<=EARLIER('CBR_REPORT'[BLD110 Build Complete FC])),[Ref ID]))

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[NessFlood]

Hi there, the formulas works for the entire table but not when I filter by PSN Region.

Without filter

But with filter for Canterbury Pilot it should only total 13 but it totals 17?

thanks again for any help 🙂

[aduguid]

Try this measure

CumulativeCompleteCount = 
CALCULATE(
    COUNTROWS(TableName), 
    FILTER(
        ALLSELECTED(TableName[Build Complete FC Date]), 
        TableName[Build Complete FC Date] <= MAX(TableName[Build Complete FC Date]) &&
        TableName[Build Complete FC Status] = "Complete"
    )
)

[NessFlood]

Thank you, I tried but it didn't like the syntax, see below