cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Milan23
Level: Power Up

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
Super User

Re: Custom Collection - Chart & CountIf

Hi Milan,

The formula to do this would look like this.

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

Hope that helps.

Super User
Super User

Re: Custom Collection - Chart & CountIf

@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
thirdimage

Power Apps Super User Class of 2020

Check it out!

thirdimage

New Badges

Check it out!

thirdimage

Power Apps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

SecondImage

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors
Users online (5,792)