Forum Discussion

CahabaData's avatar
CahabaData
Icon for Memorable Member rankMemorable 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

CahabaData's avatar
CahabaData
Icon for Memorable Member rankMemorable Member
9 years ago

Is your post a question about how to implement in the Microsoft Access database? -or- are you seeking to implement this in Power BI?

B_33's avatar
B_33
New Member
9 years ago

its about how to implement this in Microsoft Access database,

 

Thanks 

  • CahabaData's avatar
    CahabaData
    Icon for Memorable Member rankMemorable Member
    9 years ago

    You should post a Microsoft Access question here:

    "https://social.msdn.microsoft.com/Forums/office/en-US/home?forum=accessdev"

     

    An application can add & store a sequential number per ID, at the time of data entry.  But a database itself does not inherently generate such a value in its tables.  But that approach would be unusual and would be problematic.  If a record is legitimately deleted - then the sequential numbering must be re-generated to fill that gap.  This is why such values are instead generated at the reporting level and not stored at the database table level.

     

    Because the value typically does not exist in the database tables one would create these values at the reporting; in Microsoft Access reports these are not stored values but part of the report object presentation; in Power BI can either use Measures or Calculated Columns.