Calculating a value stored in CDS from PowerApps

[PowerAppsDawg]

Hi everyone,

now this is probably super simple but I cant see it.

All i want to do is show the total from a numeric column in a CDS database in my powerapp. But I absolutely cannot figure out how to do it? 

the column holds the distance covered for each record. 

[EricRegnier]

Hi @PowerAppsDawg,

To confirm did you want to create a calculated field to your CDS entity? If so, this is supported. Here's the Microsoft doc on how to create these types of attributes/columns/fields: https://docs.microsoft.com/en-us/powerapps/maker/common-data-service/define-calculated-fields

Hope this helps! 

[v-siky-msft]

Hi @PowerAppsDawg ,

Do you mean you want to show the total number of distance in PowerApps?

Just apply the following code to Text property of Label.

Sum('Entity Name', 'Distance Field')

Hope this helps.

Sik

[cchannon]

Yeah, your issue sounds simple, but it is more complex than you think, here's the Why and a couple options to get what you are looking for:

OK, so you are asking to sum a given col over the whole table so you know the grand total of that col. Let's call the table Contacts and the col Distance.

First, the ability to do this doesn't strictly exist in PowerApps, but we can do some things to get you close and hopefully it will work for your scenario. The reason it doesn't really exist is because that problem--Sum(Contacts.Distance)--could potentially be computationally enormous if you have, say, a million contacts in your database. But, you can perform sums in PowerApps--within reason--to get your result.

PowerApps gives you a basic Sum operator:

Sum(Contacts, Contacts.Distance)

... which should give you what you need if you only have a very small number of records.

I say very small because PowerApps will not send this query to SQL in a way that can be delegated, so it will only return the Sum for the top X number of rows (by default, 500).

Now, say you have 501 rows that you want to sum, and not 500. In that case, your sum will omit the last row entirely, which might be a problem for you. To overwrite this cap, open your PowerApp and navigate to Settings --> Advanced Settings. Find the setting called, "Data Row Limit for Non-Delegable Queries" - it should be set to 500 by default, but you can raise this number to whatever number reflects the actual size of the table, BUT BE CAREFUL: while raising this a small amount (say, to 1000) will probably have very little noticeable impact, raising it too large will cause noticeable hits to the app's performance. Raise it too much and your users might need to start taking coffee breaks every time they open the app.

But let's say your table is big - too big for you to be able to do this in PowerApps without crushing performance. In that case, I would recommend you take a different approach entirely. When you have a big calculation like this that takes a long time to compute, best practice is usually to keep it up to date only within a certain timeframe. Maybe you recalculate every day, or every hour, or every 5 minutes, but you do it in the background and you don't do it every time a user opens the app. PowerAutomate is a great tool for building a calculation like this. You can set it to a schedule, and it can run MUCH bigger queries, with delegation and wait as long as it takes to get a response, without any users getting frustrated with the experience.

Set up a custom entity called, "Calculated values" and put a field on it, "Total Contact Distance" then every 10 minutes have a PowerAutomate Flow automatically run the Sum(Contacts.Distance) and stick the value in a static record. Boom. You now have your sum (or at least something very very close to it) and instead of needing to query millions of records in your powerapp to get it, you just need to pull one.

Enjoy - and please accept this post as the answer if it has solved your issue!

[goDynamic]

Hi,

1. Rollup field on CDS is one option but it is not a real time refresh.
2. Use formulas in power app but you will have delegation issue if the record count exceeds

3. This option will ideally help you. You may trigger power automate to calculate the sum of distance from the retrieved records. Display sum on your PowerApps screen.

 Thanks