Adding a column to calculate running total in a collections

[skim32]

My goal is to have a collection that pulls data from a SharePoint list.  The goal is to add a column to the collection that calculates a running total.  Please see table below:

Priority	Month	VOLUME	[CALCULATUTED RUNNING TOTAL]
1	JAN	100	100
2	FEB	200	300
3	MAR	300	600

So here is my code:

ClearCollect(
    colRunTot,
    AddColumns(
        Sort(
            'SHAREPOINT LIST',
            Priority,
            SortOrder.Ascending
        ),
        "Running Total",
        Sum(
            Filter(
                'CMS Process Tracker',
                Priority =< ThisRecord.Priority
            ),
            'VOLUME'
        )
    )
);

So this code fails and generates the following error: "Error when trying to retrieve data from the network: Fetching items failed.  Possible invalid string in filter query.  clientRequeswtID: XXXX".  The part of the code that is failing is the Filter Condition "Priority <= ThisRecord.Priority".  This condition is supposed to sum up all of the VOLUME but for those where the priority is less that this record's Priority.  But it fails.  If I change that condition to "Priority <> 3", then it doesn't throw an error and it collects data.  So something about the ThisRecord.Priority is throwing the error.  (NOTE:  All priority is a unique identifier column.  like an index.)  Any help would be appreciated.

[AARON_C]

Hi @skim32 

You need to put the less than symbol before the equal sign.

Having your initial data source as the items property of your gallery, and adding a label control with below code works:

Sum(
  Filter(
    'CMS Process Tracker',
    Priority <= ThisItem.Priority
  ),
  'VOLUME'
)

Please tick Accept as solution if the answer is useful.

Thanks,

@AARON_C 

[skim32]

Thank you for the suggestion.  I am actually using that to display the running total in my gallery.  My problem is I need to display a cutline. If ThisItem.Volume = varCutLine, then fill the background with an orange color.  For example.  If Set the varCutLine variable to 300, the it would highlight the second row orange.  When I use the method in the gallery, it displays the running total fine.  I do not know how to conditionally fill the row based on cutline value.  

If the running total is stored somewhere.  Like a collection, I can then do a filter running total where it's less than the cutline and then return the First value.  Then I can use that conditionally fill the row.

I will mention that I got all this working using a power auotmate to fill out a running total column in the sharepoint list.  It works well, but to it's a little inefficient as the power automate needs to run and update the entire list each time something is changed, added or updated.

If I can somehow store the running total in collection.  It would be way more efficient.  

[AARON_C]

@skim32 

You can use the Formula property of the app to set your variable.

CutLine = 400;

\\ Please note, when using the Formula property, you need to add ; at the end of your funtcion.

To highlight your row, set the Fill property of the Volume to:

If(Value(Self.Text) = CutLine, Color.Orange, Color.Transparent)

Please tick Accept as solution if the answer is useful.

Thanks,

@AARON_C 

[skim32]

Thanks for the reply.  So the cutline has to be dynamic as well.  My example table is a very simplified example.  The real list has 300+ rows.  And if the user determines the cutline for this year is 5000.  Then the row with the running total that is closest to 5000 (but without going over) will be highlighted.  And the user can also adjust the cut line if needed without having to contact me as the developer.  So the cutline is not hard coded.  Also, let say some of the values change.  Lets say in my example above, I set cut line to 200.  Then row 1 will be highlighted.  But lets say I edit the volume for row 2 to 100.  Then the running total will then be 200 for row 2, so it will highlight row 2 instead.  

With that said, I was able to achieve this with power automate and a running total column in my share point list.  The way I did it was the power automate will automatically update the running total any time values are changed.  Then what I did was set(varCutlineRow, Last(Filter(SHAREPOINT LIST, Running total <= varCutLine))).  This sets a variable called varCutLineRow to the running total value just below the cutline.  Then, i used fill property to conditionally format the row where thisitem.running total equals  varCutLineRow.  This allows the cutline to move if volume changes fo rrows.  And also allows the user to set the cutline (granted cutline isn't changed often and is usually set a the beginning of the year like a budget.  But it does need to be changeable by the end user.

What would be super helpful is to get my original collection Idea to work.  To be able to add a column to a collection that calculates the running total.  Instead of relying on power automate.  The issue with relying on power automate is that it needs to run a flow against a large list when the volume is updated.  Or if an item is removed.  Or if an item is added.  And the way I got it to work currently is a powerapp instant flow that run on select of buttons. The experieince is also less than ideal because the user has to wait until the flow is completed before the totals and cutline gets updated in my gallery.  So Ideally, it would be to somehow use collections to collect the list. Use add columns to add a running total column.  Each row should sum up all of the running totals for each previous item and itself.  Once I do that, I can use conditions in power app properties to achieve everything I need. If I can figure out why "Priority =< ThisRecord.Priority" is not a valid condition in a collection.  If it's no valid, what is an alternative to achieve this condition. or a workaround.