cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Sephiel
Helper II
Helper II

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
v-xida-msft
Community Support
Community Support

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
Helper II
Helper II

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!

v-xida-msft
Community Support
Community Support

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.
Sephiel
Helper II
Helper II

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
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

May UG Leader Call Carousel 768x460.png

June User Group Leader Call

Join us on June 28 for our monthly User Group leader call!

PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

PA.JPG

New Release Planning Portal (Preview)

Check out our new release planning portal, an interactive way to plan and prepare for upcoming features in Power Platform.

Top Solution Authors
Users online (2,253)