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

v-haibl-msft's avatar
v-haibl-msft
Microsoft Employee
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

  • CahabaData's avatar
    CahabaData
    Memorable Member
    9 years ago

    thanks both H's....   it wasn't clear to me with DAX whether it could cumulate in this scenario without any sequencing field - it seemed feasible to think that it could as there is a row context and the Group ID....  

     

    so glad to have a final confirm on this topic.  both approaches you have supplied are very feasible.

     

     

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

      CahabaData

       

      I don’t think it could cumulate in this scenario without any sequencing field. The Group ID or other columns don’t have any order, so we need a sequencing column to do the calculation.

       

      Best Regards,

      Herbert