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

#### Celebrating the May Super User of the Month: Laurens Martens

@LaurensM  is an exceptional contributor to the Power Platform Community. Super Users like Laurens inspire others through their example, encouragement, and active participation. We are excited to celebrated Laurens as our Super User of the Month for May 2024.   Consistent Engagement:  He consistently engages with the community by answering forum questions, sharing insights, and providing solutions. Laurens dedication helps other users find answers and overcome challenges.   Community Expertise: As a Super User, Laurens plays a crucial role in maintaining a knowledge sharing environment. Always ensuring a positive experience for everyone.   Leadership: He shares valuable insights on community growth, engagement, and future trends. Their contributions help shape the Power Platform Community.   Congratulations, Laurens Martens, for your outstanding work! Keep inspiring others and making a difference in the community!   Keep up the fantastic work!

#### Announcing Power Apps Copilot Cookbook Gallery

We are excited to share that the all-new Copilot Cookbook Gallery for Power Apps is now available in the Power Apps Community, full of tips and tricks on how to best use Microsoft Copilot as you develop and create in Power Apps. The new Copilot Cookbook is your go-to resource when you need inspiration--or when you're stuck--and aren't sure how to best partner with Copilot while creating apps.   Whether you're looking for the best prompts or just want to know about responsible AI use, visit Copilot Cookbook for regular updates you can rely on--while also serving up some of your greatest tips and tricks for the Community. Check Out the new Copilot Cookbook for Power Apps today: Copilot Cookbook - Power Platform Community.  We can't wait to see what you "cook" up!