cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Sephiel
Level: Powered On

Pie chart: Count of yes/no column fields from a filtered table?

Hey Community,

 

I would like to display a pie chart showing the count of yes/no answers from a column from a SharePoint list. I have gotten a pie chart to display yes/no count from all records, but I want one that displays a filtered table.

 

'Trial Progress List' is my data source, 'Trial Manager' is the column I want to filter by (show only records that are managed by "DeWayne Harper"), "Original_x0020_Data_x0020_Receiv" is the column I want to show the count of Yes/No.

 

AddColumns(GroupBy(Filter('Trial Progress List', 'Trial Manager'.Value="DeWayne Harper"), "Original_x0020_Data_x0020_Receiv", "DataCount"), "Count", CountRows("DataCount"))

 

But this doesn't display anything...

 

When I take out the filter function and replace with only my data source, the pie chart displays all records with counts of yes/no. I also have a table with ONLY the filter function to make sure the filter works properly, and it does. (It's a small list, well under 500 records.)

 

Any ideas where I might be going wrong? Thanks much!

 

1 ACCEPTED SOLUTION

Accepted Solutions
Sephiel
Level: Powered On

Re: Pie chart: Count of yes/no column fields from a filtered table?

Thanks again for the help. I figured this was a different enough request to warrant a different thread, since it displays the Yes/No ratio while filtering a separate column. Yes, the Trial Manager column is a choice column, which worked great with your code example.

 

Your code guided me to the right solution, though I made a couple of tiny tweaks:

 

chart.PNG

Then, I was able to take it a step further and add a label control to show the percentage by adapting the code like the other example you linked earlier from my other post:

 

chart 2.PNG

 

For anyone else attempting this, I was able to take the first image's code and basically copy and paste it into the label control inside the Text and Lookup functions, BUT the important difference was that CountRows('Trial Progress List') would return the percentage of true/false based on all rows, not just those where Trial Manager = DeWayne Harper. I needed to change that code to include Filter('Trial Progress List', 'Trial Manager'.Value="DeWayne Harper"), so just know you'll need to use the filter in both parts of the code in order to get the proper percentage values.

 

Kris, thank you very much again for the help and really clear examples with my data columns. Slowly gaining confidence and getting results that are working. I appreciate it!

View solution in original post

5 REPLIES 5
Community Support Team
Community Support Team

Re: Pie chart: Count of yes/no column fields from a filtered table?

Hi @Sephiel,

Could you please share a bit more about your scenario?

Could you please share more details about the 'Trial Manager' column? Is it a Choice type column?

I found that you have post similar issue within this forum, please check my response to your issue within the following thread:

https://powerusers.microsoft.com/t5/General-Discussion/Help-showing-percentage-pie-chart-from-yes-no...

Please take a try to modify your formula as below (set the Items property of the Pie chart control to following):

AddColumns(
GroupBy(
Filter('Trial Progress List', 'Trial Manager'.Value="DeWayne Harper"),
"Original_x0020_Data_x0020_Receiv",
"DataCount"
),
"Percentage",
CountRows("DataCount")/CountRows("Trial Progress List")
)

Then display the Percentage column within the Labels field and Series field as below:1.JPG

 

 

Best regards,

Kris

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Sephiel
Level: Powered On

Re: Pie chart: Count of yes/no column fields from a filtered table?

Thanks again for the help. I figured this was a different enough request to warrant a different thread, since it displays the Yes/No ratio while filtering a separate column. Yes, the Trial Manager column is a choice column, which worked great with your code example.

 

Your code guided me to the right solution, though I made a couple of tiny tweaks:

 

chart.PNG

Then, I was able to take it a step further and add a label control to show the percentage by adapting the code like the other example you linked earlier from my other post:

 

chart 2.PNG

 

For anyone else attempting this, I was able to take the first image's code and basically copy and paste it into the label control inside the Text and Lookup functions, BUT the important difference was that CountRows('Trial Progress List') would return the percentage of true/false based on all rows, not just those where Trial Manager = DeWayne Harper. I needed to change that code to include Filter('Trial Progress List', 'Trial Manager'.Value="DeWayne Harper"), so just know you'll need to use the filter in both parts of the code in order to get the proper percentage values.

 

Kris, thank you very much again for the help and really clear examples with my data columns. Slowly gaining confidence and getting results that are working. I appreciate it!

View solution in original post

JV_DXB
Level: Powered On

Re: Pie chart: Count of yes/no column fields from a filtered table?

Hello @Sephiel @v-xida-msft ,

 

I an new to Powerapps and trying to bring a Pie Chart to my app. I have linked my data source (Excel) to the app. Below is what I have:

Source: Table1

Column: Issue Status

Issue Status column has two status - Open or Closed

What  I want to show on Pie Chart

Show the number (Count) of Open / Closed items on the Pie Chart.

Highlighted
Sephiel
Level: Powered On

Re: Pie chart: Count of yes/no column fields from a filtered table?

Hello! It sounds like you have a good start getting data loaded. You can add a pie chart from the insert tab, and change the data source in the Items property by typing in Table1. This will get you a breakout of every row in the pie chart.

 

Capture.PNG

 

In order to show a count of each status, we need to group the status options together so the pie chart only shows the two options.

 

GroupBy(Table1,"Issue Status","GroupChoice")

 

GroupChoice is the variable name we created.

 

Then we need to create a column (because a lot of objects operate on having an imaginary table of data containing our table data that we can manipulate) to count the number of times Issue Status is Open or Closed. We tell it to use our GroupBy code as its source, name our column ("GroupStatus") and then tell it the expression we want it to use for our pie chart. We want it to count the rows of our two choices, so we say CountRows(GroupChoice). Here it is all together, in the Items property of the pie chart:

 

AddColumns(GroupBy(Table1,"Issue Status","GroupChoice"),"GroupStatus",CountRows(GroupChoice))

 

Capture2.PNG

 

Now as far as I know you can't change the label directly except by the column we created (and it will show an unformatted percentage) so instead I find it easier to create a label object from the insert screen, and we manipulate the Text property to show a count of the Issue Status rows that are "Open" or "Closed".

 

To do this we do the same as before and start with GroupBy, the same expression as before. Then we nest it in AddColumns like before, only to denote that the label is for the count of one or the other statuses, change the column name to reflect that - "OpenCount" is what I used here.

 

AddColumns(GroupBy(Table1,"Issue Status","GroupChoice"),"OpenCount",CountRows(GroupChoice))

Then lastly, we need to make sure we are counting only open statuses, so we nest our function into a LookUp function, where we tell it to filter the Issue Status column and tell it the result we want is the column "OpenCount" that we created. Here it is all together:

LookUp(AddColumns(GroupBy(Table1,"Issue Status","GroupChoice"),"OpenCount",CountRows(GroupChoice)),'Issue Status'="Open",OpenCount)


Capture3.PNG

Create another label and change the column name in AddColumns to "ClosedCount" and 'Issue Status' = "Closed", and the lookup result to ClosedCount to get the other count.

 

Just a warning that in this example code we are identifying multiple objects with the same column name "GroupChoice". If you only have a couple objects in your app using the column name you'll be fine, but best practice is really to define a different column name for every object. A variable name definition repeated too many times will cause lag and your data not to update properly. You could do "GroupChoiceChart" and "GroupChoiceOpenLabel", "GroupChoiceClosedLabel" for example.

 

Hope this helps. Keep at it, don't give up. It took me months of working in PowerApps to be able to get through things like this and I still get tripped up all the time. But after some practice it will click and you'll be off and running!

JV_DXB
Level: Powered On

Re: Pie chart: Count of yes/no column fields from a filtered table?

@Sephiel 

 

That worked perfectly. Thank you.

Helpful resources

Announcements
thirdimage

Power Automate 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

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors (Last 30 Days)
Users online (5,652)