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

Help showing percentage pie chart from yes/no column in SharePoint list?

Hello!

I'm really new to PowerApps and not really a coder, but as I'm learning, I'm trying to make some simple tools to display percentage progress of research trials.

 

I have a SharePoint list named "Trial Progress List" with Yes/No columns "Original Protocol Received", "Original Data Received", etc. based on whether we have received a protocol file or data files for a research trial.

 

I need to show separate pie charts that show the percentages of "Yes" and "No" for each column.

The list and columns have spaces, so knowing where to put a _x0020_ will be helpful.

 

Capture.PNG

 

 

This is as far as I have gotten. I've tried to look into AddColumn, GroupBy, but I can't seem to find an example that uses a SharePoint list rather than an imported table to make this work, and I don't completely understand the conditions required of these functions. I'm pretty new to the syntax of this software. Any explanation you can provide would be extremely helpful.

Thanks very much!

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Help showing percentage pie chart from yes/no column in SharePoint list?

Hi @Sephiel,

Could you please share a bit more about your scenario?

Do you want to display percentage of "Yes" or "No" value for each column using Pie chart control?

The user @myonlywan has faced similar issue with you, please check the following thread:

https://powerusers.microsoft.com/t5/General-Discussion/bar-chart-based-on-star-rating/m-p/142099

I have created a SP list on my side, the data structure as below:1.JPG

I have made a test on my side, please take a try with the following workaround:2.JPG

Set the Items property of the Pie Chart control (Pie Chart1) to following formula (For Original Protocol Received column):

 

AddColumns(
GroupBy('20181112_case2',"Original_x0020_Protocol_x0020_Re","GroupData"),
"Percentage",
CountRows(GroupData)/CountRows('20181112_case2')
)

On your side, it should be:

 

AddColumns(
GroupBy('Trial Progress List',"Original_x0020_Protocol_x0020_Re","GroupData"),
"Percentage",
CountRows(GroupData)/CountRows('Trial Progress List')
)

Set the Items property of the Pie Chart control (Pie Chart2) to following formula (For Original Data Received column):

 

AddColumns(
GroupBy('20181112_case2',"Original_x0020_Data_x0020_Receiv","GroupData"),
"Percentage",CountRows(GroupData)/CountRows('20181112_case2')
)

On your side, it should be:

AddColumns(
GroupBy('Trial Progress List',"Original_x0020_Data_x0020_Receiv","GroupData"),
"Percentage",
CountRows(GroupData)/CountRows('Trial Progress List')
)

Currently, within Pie Chart control, we could not display the detailed percentage number directly. If you want to display the detailed percentage number, you could consider take a try to add a Label control to display the detailed percentage number. Please take a try with the following workaround:3.JPG

Within the Pie Chart1, for True Pie item, set the Text property of the Label control to following:

Text(
LookUp(
AddColumns(GroupBy('Trial Progress List',"Original_x0020_Protocol_x0020_Re","GroupData"),"Percentage",CountRows(GroupData)/CountRows('Trial Progress List')),
Original_x0020_Protocol_x0020_Re <> false,Percentage
)*100,
"[$-en-US]#.00"
) &"%"

for False Pie Item, set the Text property of the Label control to following:

Text(
LookUp(
AddColumns(
GroupBy('Trial Progress List',"Original_x0020_Protocol_x0020_Re","GroupData"),"Percentage",CountRows(GroupData)/CountRows('Trial Progress List')),
Original_x0020_Protocol_x0020_Re <> true,Percentage
)*100,
"[$-en-US]#.00"
) &"%"

Similar formula for Label control in Pie Chart 2 control.

 

More details about GroupBy function, please check the following article:

GroupBy function

 

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.

View solution in original post

3 REPLIES 3
Sephiel
Level: Powered On

Re: Help showing percentage pie chart from yes/no column in SharePoint list?

After some extra reading, I understand better how PowerApps creates and uses tables. I was able to get an AddColumns and GroupBy function to work here to sum the "true/false" answers:

 

Capture.PNG

 

 

Now I am trying to change the count function to something that will display the percentages instead of "true" and "false" and getting stuck at that point. I had seen another example where GroupBy's expression is a Text function that uses the Sum function to express fractions of my column records (am I understanding that correctly?), but as you can see in this screenshot, it's not displaying a percent, nor is it grouping the yes/no properly. (Protocols Received is supposed to be 100% but Data Received is supposed to be the same fraction as my count attempt on the left.)

 

Capture2.PNG

 

Any thoughts on where I went wrong converting to a percentage label? Appreciate any insight!

Community Support Team
Community Support Team

Re: Help showing percentage pie chart from yes/no column in SharePoint list?

Hi @Sephiel,

Could you please share a bit more about your scenario?

Do you want to display percentage of "Yes" or "No" value for each column using Pie chart control?

The user @myonlywan has faced similar issue with you, please check the following thread:

https://powerusers.microsoft.com/t5/General-Discussion/bar-chart-based-on-star-rating/m-p/142099

I have created a SP list on my side, the data structure as below:1.JPG

I have made a test on my side, please take a try with the following workaround:2.JPG

Set the Items property of the Pie Chart control (Pie Chart1) to following formula (For Original Protocol Received column):

 

AddColumns(
GroupBy('20181112_case2',"Original_x0020_Protocol_x0020_Re","GroupData"),
"Percentage",
CountRows(GroupData)/CountRows('20181112_case2')
)

On your side, it should be:

 

AddColumns(
GroupBy('Trial Progress List',"Original_x0020_Protocol_x0020_Re","GroupData"),
"Percentage",
CountRows(GroupData)/CountRows('Trial Progress List')
)

Set the Items property of the Pie Chart control (Pie Chart2) to following formula (For Original Data Received column):

 

AddColumns(
GroupBy('20181112_case2',"Original_x0020_Data_x0020_Receiv","GroupData"),
"Percentage",CountRows(GroupData)/CountRows('20181112_case2')
)

On your side, it should be:

AddColumns(
GroupBy('Trial Progress List',"Original_x0020_Data_x0020_Receiv","GroupData"),
"Percentage",
CountRows(GroupData)/CountRows('Trial Progress List')
)

Currently, within Pie Chart control, we could not display the detailed percentage number directly. If you want to display the detailed percentage number, you could consider take a try to add a Label control to display the detailed percentage number. Please take a try with the following workaround:3.JPG

Within the Pie Chart1, for True Pie item, set the Text property of the Label control to following:

Text(
LookUp(
AddColumns(GroupBy('Trial Progress List',"Original_x0020_Protocol_x0020_Re","GroupData"),"Percentage",CountRows(GroupData)/CountRows('Trial Progress List')),
Original_x0020_Protocol_x0020_Re <> false,Percentage
)*100,
"[$-en-US]#.00"
) &"%"

for False Pie Item, set the Text property of the Label control to following:

Text(
LookUp(
AddColumns(
GroupBy('Trial Progress List',"Original_x0020_Protocol_x0020_Re","GroupData"),"Percentage",CountRows(GroupData)/CountRows('Trial Progress List')),
Original_x0020_Protocol_x0020_Re <> true,Percentage
)*100,
"[$-en-US]#.00"
) &"%"

Similar formula for Label control in Pie Chart 2 control.

 

More details about GroupBy function, please check the following article:

GroupBy function

 

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.

View solution in original post

Sephiel
Level: Powered On

Re: Help showing percentage pie chart from yes/no column in SharePoint list?

Thanks very much! This worked perfectly. I didn't realize that it was going to work better to use a separate label to display the percent, rather than try to change the label in the PieChart. That makes so much more sense!

 

Also, thank you so much for the specific code examples. I've had trouble understanding what is a function word and what is an example column, etc. so having you reference my specific columns was VERY helpful!!

Helpful resources

Announcements
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 Kudoed Authors (Last 30 Days)
Users online (4,080)