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

[Anonymous]

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.

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!

[Anonymous]

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:

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.)

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

[v-xida-msft · Accepted solution]

Hi @Anonymous,

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:

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

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:

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

[Anonymous]

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!!