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
CahabaData
Memorable Member
Memorable Member

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                $3

   B               2                $1

   B               6                $4

 

Result Sought:

Group    Service ID    Amount   GroupSum
   A             1                 $2              $2
   A             4                 $1              $3
   A             2                 $3              $6

   B             2                 $1              $1
   B             6                 $4              $5

 

either Measure or Calculated column.... at Dax Patterns every example has a date field involved.....kind of going in circles on this one at the moment.......wondering if the lack of a sequential field is my issue or just general brain fog..........

 

 

www.CahabaData.com
2 ACCEPTED SOLUTIONS
Habib
Responsive Resident
Responsive Resident

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 🙂

View solution in original post

v-haibl-msft
Employee
Employee

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

 

Running Total cumulative by Group_1.jpg

 

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

Running Total cumulative by Group_2.jpg

 

Best Regards,

Herbert

View solution in original post

9 REPLIES 9
Anonymous
Not applicable

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 🙂

B_33
New Member

Hello All,

 

I am extremly new to the use of access so please bare with me if I do not understand any respones you have intitially. I have been trying to solve a problem I have within Access but I have not been able to find out any information specific to what I needd my Access database to do:

 

- I need to create a seqntial number based on the total of records for a customer

-I need this number to reset with every year.

-I need this number to be specific to sopecific to the customer.

-I'm not sure if I should create it as a query which appends the information to a table or as a Before change data macro.

 

Please help. I am really stuck and I'm at a dead end.

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

www.CahabaData.com

its about how to implement this in Microsoft Access database,

 

Thanks 

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.

 

 

www.CahabaData.com
v-haibl-msft
Employee
Employee

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

 

Running Total cumulative by Group_1.jpg

 

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

Running Total cumulative by Group_2.jpg

 

Best Regards,

Herbert

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.

 

 

www.CahabaData.com

@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

Habib
Responsive Resident
Responsive Resident

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 🙂

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.