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
bricwood
Helper I
Helper I

Reset Cumulative Sum/Total Based on a Column Value

I have a set of data that I need to have a running total for but I need it to reset based on the value of another column.

 

The total will increment when one of the columns value is null, once it is not null the total will reset to zero then continue.

 

Additionally, I need to somehow reset it to zero or group it based on another column (like a category).

 

I have attached what I was trying to do but it is far out of my abilities...

 

 

IncrementalSuffering.png

6 REPLIES 6
mahoneypat
Employee
Employee

This measure expression gets your expected results from the example data.

Count Since Last Yes =
VAR thisdatetime =
    MIN ( 'Action'[Date] )
VAR lastYes =
    CALCULATE (
        MAX ( 'Action'[Date] ),
        ALL ( 'Action'[Date] ),
        'Action'[Date] < thisdatetime,
        'Action'[Action] = "Yes"
    )
VAR ifblank =
    IF (
        ISBLANK ( lastYes ),
        CALCULATE ( MIN ( 'Action'[Date] ), ALLEXCEPT ( 'Action', 'Action'[Category] ) ) - 1,
        lastYes
    )
RETURN
    IF (
        MIN ( 'Action'[Action] ) <> "Yes",
        CALCULATE (
            COUNTROWS ( 'Action' ),
            ALL ( 'Action'[Date] ),
            'Action'[Date] <= thisdatetime,
            'Action'[Date] > ifblank
        ) - 1
    )

 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


himanshuxa720
New Member

did you get the answer to this?

I ended up making a Powershell script that did the work for me. Nothing else really worked.

Greg_Deckler
Super User
Super User

Not positive, but this sounds like you are in need of something along the lines of Cthulhu: https://community.powerbi.com/t5/Quick-Measures-Gallery/Cthulhu/td-p/509739


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

@Greg_Decklerthat looks great but these rows are consecutive..

 

Would you suggest I change the data in a way that would allow this to work? If so, any tips?

 

Thank you for getting back to me.

Yeah, I think if you add an Index column to this in Power Query that you are pretty much there. To be clear, Cthulhu doesn't work without consequetive data. There pretty much has to be an Index. The only wrinkle you have is that you have to account for the extra "Category" column but that shouldn't be too much of a stretch.


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

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.