cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Nested rollup fields

Hi,

I have the following related entities:

 

[ProductFromSupplier]-->[Product]-->[ProductForProject]-->[Project]

 

each with a one to many relation.

 

I wish to achieve the following rollup fields in order to calculate the total cost for products assigned to projects:

  • a [ProductFromSupplier] has the currency field [Price] in which a static amount is entered.
  • a [Product] has a field [AveragePrice] which should hold the (rollup) calculation of the average price of all related [ProductFromSupplier]
  • a [ProductForProject] has a field [quantity] and a (rollup) field [TotalPrice] which should be [Product.AveragePrice]*[Quantity]
  • a [Project] has a (rollup) field [TotalProjectPrice] which should be the Sum of all [ProductForProject.TotalPrice]

I have difficulties on setting this nested rollup calculation: it seems that I cannot create a rollup field of another rollup field, but then, how can I achieve this anyhow?

 

Thanks,

Koen

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

The way to do calculations is to create a variable and list records then loop through the list incrementing the variable

 

see https://youtu.be/7sCF2pg8q5I

so you would need a variable for the total, then you can get the total number using a length expression in a compose field

 

then do another compose dividing your sum variable by the total number to get your average

View solution in original post

7 REPLIES 7
Highlighted
Super User III
Super User III

Koen,

 

Here are the general rules of rollup fields and calculated fields:

  • rollup fields can roll up calculated fields that don't reference other calculated fields
  • Rollup fields cannot roll up other rollup fields

With that in mind, rollup and calculated fields likely are not the best fit for what you want to do. Also rollup fields only roll up every 12 hours, so IMO they are of limited use for decision making for anything but slowly changing values.

 

Look to use scheduled Power Automate jobs or CDS plugins for these more advanced calculation and rollup purposes.

Highlighted

Hi @jlindstrom , thanks for your answer. As you suggested, I started to create a scheduled flow that would calculate the average price for each [Product] as the average of all related [ProductFromSupplier.Price].

 

I have created a flow that Lists all records of [Product]; then I added a Apply To Each task in which I want to set the [Product.AveragePrice] to something like "Average of (Related.ProductFromSupplier).Price"; how do I get this average? Should I use an additional action "List Records" to get all ProductFromSupplier with a filter where ProductFromSupplier.Product = Product, or is there another way?

 

Thanks,

Koen

Highlighted

The way to do calculations is to create a variable and list records then loop through the list incrementing the variable

 

see https://youtu.be/7sCF2pg8q5I

so you would need a variable for the total, then you can get the total number using a length expression in a compose field

 

then do another compose dividing your sum variable by the total number to get your average

View solution in original post

Highlighted

@jlindstrom Thanks, I would never have found that!

I can hardly believe that (like also mentioned in the video, and being the year 2020), I still have to manually fetch all the related items, loop through them, sum up all the prices and divide by the number of records... I thought there would be an easy way to get an average of a related dataset... Anyway, I will do it like that for now, but I hope that in the near future a more common way of doing calculations with datasets is coming...

Highlighted

Also if you don’t get all records in your sum change the pagination settings on list records

Highlighted

@jlindstrom  Does Microsoft thinks getting an average of related items is rocket science?

Why isn't there an equivalent of the SQL way:

 

SELECT
  AVG(price),
  otherId
FROM Products
GROUP BY otherId;

 

Now I have to define 4 variables (tempSum, Sum, Counter, Average) and loop over all products manually (also taking care of dividing by zero for the average when no related products are found), giving this HUGE flow...

image.png

Highlighted

actually I have another suggestion. I haven't tried this yet, but it should work.

 

create your flow in a solution

use the common data service (current  environment) connector

List records supports fetch

use an aggregate fetch query  https://docs.microsoft.com/en-us/powerapps/developer/common-data-service/use-fetchxml-aggregation

Then you can get the aggregate values from list record

 

so you can get aggregate list records results

 

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Community Highlights

Community Highlights

Check out the Power Platform Community Highlights

Users online (8,099)