Forum Discussion

CahabaData's avatar
CahabaData
Memorable Member
9 years ago
Solved

Running Total / cumulative by Group

Can't seem to get the handle on this.  Starting data:   Group     Service ID     Amount    A               1                $2    A               4                $1    A               2        ...
Need Help
  • Habib's avatar
    Habib
    9 years ago

    Hi CahabaData you are true, lack of sequential field is the major issue.

     

    You can achieve this using below steps.

     

    1 -  Add a sequence number. You can use RANKX function for this.

     

    Rank = RANKX(SampleData,SampleData[Service ID],,ASC,Dense)

    2 - Add RunningTotal using below formula

     

    RunningTotal =
CALCULATE (
    SUM ( SampleData[Amount] ),
    ALLEXCEPT ( SampleData, SampleData[Group] ),
    SampleData[Rank] <= EARLIER ( SampleData[Rank] )
)

    This will give you desired results :)

  • v-haibl-msft's avatar
    v-haibl-msft
    9 years ago

    CahabaData

     

    You can first add an index column in Query Editor and then create a calculated column with EARLIER function to get the expected result.

     

     

    GroupSum = 
SUMX (
    FILTER (
        Table1,
        EARLIER ( Table1[Group] ) = Table1[Group]
            && EARLIER ( Table1[Index] ) >= Table1[Index]
    ),
    Table1[Amount]
)

     

    Best Regards,

    Herbert

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hi all and thanks for your help on this topic,

 

One question here:

 

how would you do if you had to groupsum by not only the column "Group" but also by "Service_ID". This means you would refer to two different column to group indicators and compute the cumulative sum. Also maybe if I have a "date" column. will this calculate the cumulative sum from the most recent to the oldest automatically or do I have to specify this?

 

Thanks a lot 🙂