Forum Discussion

NessFlood's avatar
NessFlood
Icon for Helper I rankHelper I
1 year ago
Solved

Cumulative Count DAX

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 colum...
  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

Anonymous's avatar
Anonymous
Not applicable
1 year ago

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's avatar
NessFlood
Icon for Helper I rankHelper I
1 year ago

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 😀