cancel
Showing results for
Did you mean:
Responsive Resident

## Need help with a collection to hold the cumulative percentages.

Im currently trying to create a collection based from another collection.  So, it's like cumulative percentages.

``````ClearCollect(
colCumulativePercentages,
ForAll(
FirstN(
Sort(
Filter(
colLastSevDaysDefects,
cmb_PartNumber_2.Selected.Value = NoParte
),
DefectCount,
SortOrder.Descending
),
10
),
{
Value: DefectCount,
Cumulative: Sum(
Filter(
colLastSevDaysDefects,
cmb_PartNumber_2.Selected.Value = NoParte &&
DefectCount <= ThisRecord.DefectCount
),
DefectCount
)
}
)
);``````

This is the result:

This is the other collection:

What can I do to accomplish this?

1 ACCEPTED SOLUTION

Accepted Solutions
Responsive Resident

I manage to get this work, but not with your solution since RowNumber is not a function in powerapps:

``````ClearCollect(
colCumulativePercentages,
ForAll(
FirstN(
Sort(
Filter(
colLastSevDaysDefects,
cmb_PartNumber_2.Selected.Value = NoParte
),
DefectCount,
SortOrder.Descending
),
10
),
{
Val: DefectCount,
CalculatedVal: ((DefectCount * 100) / ( Sum(
FirstN(
Sort(
Filter(
colLastSevDaysDefects,
cmb_PartNumber_2.Selected.Value = NoParte
),
DefectCount,
SortOrder.Descending
),
10
),
DefectCount
) ))
}
)
);``````

4 REPLIES 4
Most Valuable Professional

Seems like you're trying to create a collection that represents the cumulative defect count for the last seven days based on another collection, `colLastSevDaysDefects`.

The issue in your current formula is that the cumulative calculation is not ordered, and it's considering all defect counts less than or equal to the current record's count.

As a result, it's summing up the same numbers for each row.

You should modify the formula to calculate the cumulative sum in the correct order. You can achieve this by using a variable to keep track of the running total as you go through the sorted collection.

Here's a corrected version of the formula:

``````ClearCollect(colCumulativePercentages, Blank()); // Initialize the collection
Set(varCumulative, 0); // Initialize the cumulative sum variable

ForAll(
FirstN(
Sort(
Filter(
colLastSevDaysDefects,
cmb_PartNumber_2.Selected.Value = NoParte
),
DefectCount,
SortOrder.Descending
),
10
),
Collect(
colCumulativePercentages,
{
Value: DefectCount,
Cumulative: Set(varCumulative, varCumulative + DefectCount)
}
)
);
``````

This formula initializes the `colCumulativePercentages` collection and `varCumulative` variable to start from zero. Then, as it goes through the sorted collection of the last seven days' defects, it adds each record's `DefectCount` to the running total and adds the result to the `colCumulativePercentages` collection.
Hope it helps @HectorOlivos

Responsive Resident

Hello @poweractivate.

I try to use your solution but I have this error in:

`` Cumulative: Set(varCumulative, varCumulative + DefectCount)``

It says: This Function cannot be invoked within ForAll

Most Valuable Professional

Yes we can't do that in Power Apps, you are right. We actually can't use Set, UpdateContext, or anything like that from inside ForAll, so that is my mistake.

So actually try below instead which avoids this:

``````ClearCollect(colSortedDefects,
FirstN(
Sort(
Filter(
colLastSevDaysDefects,
cmb_PartNumber_2.Selected.Value = NoParte
),
DefectCount,
SortOrder.Descending
),
10
)
);

ClearCollect(colCumulativePercentages, Blank());

ForAll(
colSortedDefects,
Collect(
colCumulativePercentages,
{
Value: DefectCount,
Cumulative: Sum(FirstN(colSortedDefects, RowNumber(colSortedDefects)), DefectCount)
}
)
);
``````

Tell me if the above works instead @HectorOlivos

Responsive Resident

I manage to get this work, but not with your solution since RowNumber is not a function in powerapps:

``````ClearCollect(
colCumulativePercentages,
ForAll(
FirstN(
Sort(
Filter(
colLastSevDaysDefects,
cmb_PartNumber_2.Selected.Value = NoParte
),
DefectCount,
SortOrder.Descending
),
10
),
{
Val: DefectCount,
CalculatedVal: ((DefectCount * 100) / ( Sum(
FirstN(
Sort(
Filter(
colLastSevDaysDefects,
cmb_PartNumber_2.Selected.Value = NoParte
),
DefectCount,
SortOrder.Descending
),
10
),
DefectCount
) ))
}
)
);``````

Announcements

#### April 4th Copilot Studio Coffee Chat | Recording Now Available

Did you miss the Copilot Studio Coffee Chat on April 4th? This exciting and informative session with Dewain Robinson and Gary Pretty is now available to watch in our Community Galleries!   This AMA discussed how Copilot Studio is using the conversational AI-powered technology to aid and assist in the building of chatbots. Dewain is a Principal Program Manager with Copilot Studio. Gary is a Principal Program Manager with Copilot Studio and Conversational AI. Both of them had great insights to share with the community and answered some very interesting questions!     As part of our ongoing Coffee Chat AMA series, this engaging session gives the Community the unique opportunity to learn more about the latest Power Platform Copilot plans, where we’ll focus, and gain insight into upcoming features. We’re looking forward to hearing from the community at the next AMA, so hang on to your questions!   Watch the recording in the Gallery today: April 4th Copilot Studio Coffee Chat AMA

#### Tuesday Tip: Blogging in the Community is a Great Way to Start

Top Solution Authors
Top Kudoed Authors
Users online (4,893)