cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
New Member

Custom Collection - Chart & CountIf

Dear all,

I got stuck today on creating custom collection with ClearCollect. I need to insert chart (pie prefferably) that will show how many projects do I have active or inactive in my Sharepoint list. I have a table similar to this:

 

ProjectStatus
Project 1Active
Project 2Inactive
Project 3Active
Project 4Active
Project 5Active
Project 6Inactive
Project 7Active

 

And I need the outcome to be like this (for the chart):

 

StatusCount
Active5
Inactive2

 

Could please anyone suggest how to accomplish this? Any help appreciated 🙂

 

Many thanks,

 

Milan

2 REPLIES 2
Super User III
Super User III

Hi Milan,

The formula to do this would look like this.

AddColumns(GroupBy(SharePointList, "Status", "Details"), 
"Count",
CountRows(Details)
)

Hope that helps.

Super User III
Super User III

@Milan23 

Assuming your collection looks like this.

 

ClearCollect(myProjects,
    {Project: "Project 1", Status:"Active"},
    {Project: "Project 2", Status:"Inactive"},
    {Project: "Project 3", Status:"Active"},
    {Project: "Project 4", Status:"Active"},
    {Project: "Project 5", Status:"Active"},
    {Project: "Project 6", Status:"Inactive"},
    {Project: "Project 7", Status:"Active"}
);

 

You can summarize the data by creating a new collection called mySummary using this code.  Make sure to put within a behaviour based property such as OnVisible, OnSelect, or OnStart.

 

ClearCollect(
    mySummary,
    ForAll(Distinct(myProjects,Status),
        {Status: Result, Count: CountRows(Filter(myProjects,Status = Result))})
);

 

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

 

Helpful resources

Announcements
New Badges

New Solution Badges!

Check out our new profile badges recognizing authored solutions!

New Power Super Users

Congratulations!

We are excited to announce the Power Apps Super Users!

Power Apps Community Call

Power Apps Community Call: February

Did you miss the call? Check out the Power Apps Community Call here.

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

Top Solution Authors
Top Kudoed Authors
Users online (76,544)