Answered

running total in a collection

(0) Share

Report

Posted on by Thrive

How do I add a running total to an existing collection below? The running total aggregates year after year within the same service. Aka, running total calculates for Service 1 from years 2021 thru 2024, then calculates a new running total for Service 2 for the same years. The example column in the table below illustrates what I want to achieve.

Service	Year	Data	Running Total column example that I want to add to this collection
Service 1	2021	10	10
Service 1	2022	10	20
Service 1	2023	5	25
Service 1	2024	2	27
Service 2	2021	1	1
Service 2	2022	1	2
Service 2	2023	6	8
Service 2	2024	3	11
etc...	etc...	etc..

Categories:

Building Power Apps

All responses (4)

Answers (1)

Sort by

BCLS776 8,994 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!
It's not exactly the answer to your question, but I wouldn't add a column specifically to do this. Instead, I would use a Sum() & Filter() together to calculate the total where/when I needed it:

Sum( Filter(myCollection, Service = "Service 1", Year <= 2023), Data )

This would calculate a value of 25 for Service 1 up to year 2023, assuming the Year column is a number. If you like, you can plus in control values or variables where this code indicates the Service and Year values.

Hope that helps,

Bryan
Verified answer

v-bofeng-msft Microsoft Employee on at

Like
a
(1)

Report
Copy link

Link copied!
Hi @Thrive :

I've made a test for your reference:

1\I assume there is a table:

ClearCollect( TheCollection, {Service:"Service1",Year:2021,Data:10,'Running Total column example that I want to add to this collection':0}, {Service:"Service1",Year:2022,Data:10,'Running Total column example that I want to add to this collection':0}, {Service:"Service1",Year:2023,Data:5,'Running Total column example that I want to add to this collection':0}, {Service:"Service1",Year:2024,Data:2,'Running Total column example that I want to add to this collection':0}, {Service:"Service2",Year:2021,Data:1,'Running Total column example that I want to add to this collection':0}, {Service:"Service2",Year:2022,Data:1,'Running Total column example that I want to add to this collection':0}, {Service:"Service2",Year:2023,Data:6,'Running Total column example that I want to add to this collection':0}, {Service:"Service2",Year:2024,Data:3,'Running Total column example that I want to add to this collection':0} )

2\Add a timer control:
Duration

500

OnSelect

Set(Totoladd,0);Set(LastSeviceName,"");Set(CountNumber,1);

OnTimerEnd

Set(CurrentItem,Last(FirstN(TheCollection,CountNumber))); Set(ServiceName,CurrentItem.Service); If( CurrentItem.Service=LastSeviceName, Set(Totoladd,Totoladd+CurrentItem.Data), Set(Totoladd,CurrentItem.Data) ); Patch( TheCollection, CurrentItem, {'Running Total column example that I want to add to this collection':Totoladd} ); Set(LastSeviceName,CurrentItem.Service); Set(CountNumber,CountNumber+1);

Repeat

CountNumber<CountRows(TheCollection)

Finally,select the timer control and you will get the result:

Best Regards,

Bof
Thrive 34 on at

Like
a
(0)

Report
Copy link

Link copied!

@v-bofeng-msft , this is awesome! Just curious, is there an advantage or need to tie this to a timer?
v-bofeng-msft Microsoft Employee on at

Like
a
(1)

Report
Copy link

Link copied!

Hi @Thrive :

Because Canvas App itself does not have a fully functional function to build a loop (the Forall function has a relatively large limit), it is necessary to use the timer control to build this loop.

Best Regards,

Bof