Calculate items in a balance column

(0) Share

Report

Posted on by dobroo

201

Hi @ALL,

can someone help me to understand how this calculation can be realized:

Title	Income	Outcome	Balance
Item1	10		10
Item2		5	5
Item3	3		8
Item4		6	2

If I edit the "Item2" from 5 to 4. How can I update the column "balance" for all the items.

Thanks for help

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (6)

Answers (0)

Hamish Sheild 3,005 Most Valuable Professional on at

Like (0)

Report

Hi @dobroo ,

You should be able to do this with a Power Automate flow. Assign an order number to each row and then use Power Automate to loop (Apply to Each) through each row, in order. Inside the Apply to Each, keep track of the balance through a Variable and update the current row to the value of the variable.

This process will have a lot of Dataverse API calls so would only work for relatively small data sets that do not change that often.

If you have a large dataset then holding the Balance in Dataverse and calculating with Power Automate is probably not a good idea. Be aware of the limits. A Power BI report might be a better solution but depends on how you want to use the Balance column.

Was this reply helpful? Yes No
dobroo 201 on at

Like (0)

Report

Is their no way to do this with a calculated column or a Canvas App and Patch-Function?

Was this reply helpful? Yes No
Drew Poggemann 9,287 Most Valuable Professional on at

Like (0)

Report

Hi Dobroo,

You might want to utilize a child entity and have this basically be the "Transaction" and you can then have a single column for the Transaction Value and it would be negative or positive where income could be positive and outcome would be negative in the values.

You can then create a rollup column in the parent entity that will sum the values of the transactions to come up with your "Balance" value.

Hopefully this helps.

Thanks,

Drew

Was this reply helpful? Yes No
Hamish Sheild 3,005 Most Valuable Professional on at

Like (0)

Report

Hi @dobroo , since you have a running balance on the line items then you can’t use calculated columns. You could do what @dpoggemann suggests and only have the balance shown on the parent entity and not have a running balance on the line items. It really depends on what the requirements are. Why do you need a running balance on the line items?

You could possibly also achieve this with a Canvas app but it depends on how the users are interacting with the data and how it gets updated. If data is coming in via integration and not direct input from a user then a Canvas app won’t work. Give the Canvas app a try and see.

Was this reply helpful? Yes No
dobroo 201 on at

Like (2)

Report

Hi @dpoggemann , @HSheild

i am not yet familiar with rollup columns and it is apparently still not possible to configure rollup columns for Teams-Apps. Or? when I go to the settings of the columns it is endless loading...

I realized my scenario with a Canvas App. this tutorial helped me:
https://matthewdevaney.com/power-apps-running-totals-in-a-gallery/

Thx for your help

Was this reply helpful? Yes No
Drew Poggemann 9,287 Most Valuable Professional on at

Like (1)

Report

Hi @dobroo
Rollup columns are available for Dataverse for Teams (see the following link: https://docs.microsoft.com/en-us/powerapps/teams/table-columns). Details specifically to do rollups, now this is not real-time and that is something you need to be aware of. It runs as a job on a scheduled basis and this may not meet your requirements...
https://docs.microsoft.com/en-us/powerapps/maker/data-platform/define-rollup-fields

Hope this works,

Thanks,

Drew

Was this reply helpful? Yes No