Power Apps Line Chart With SharePoint Yes/No Column

A few days before, I worked on building an employee survey application in the Power Apps canvas app. While creating the dashboard, we must display the percentage of employees who give feedback on the “Training required” in the line chart.

In this article, I will explain the Power Apps line chart with SharePoint Yes/No column.

Power Apps Line Chart With SharePoint Yes/No Column

Look at the Power Apps dashboard below; it displays the percentage of employees who choose training required and not required. We used the SharePoint list as datasource for this application, and this training required a Yes/No field.

Here is the SharePoint list [Employee Satisfaction Survey] that contains employee survey details.

These are the columns and their data types for the above SharePoint list.

Column Name	Data Type
Full Name	Title
Email	Single line of text
Phone Number	Single line of text
Department	Choice(IT,HR,Finance, Marketing,Sales)
Trainings Required	Yes/No
Job Satisfaction	Yes/No

1. Connect the SharePoint list with the Power Apps application. Then, create a collection on the OnStart property of the App object.

// Collection that stores true, false values in Yes/No

ClearCollect(ColEmpSatisfactionSurvey,AddColumns('Employee Satisfaction Survey',cusTrainingsRequired,If('Trainings Required'=true,"Yes","No")));

//Collection for line chart

ClearCollect(colEmpTrainings,AddColumns(AddColumns(GroupBy(ShowColumns(ColEmpSatisfactionSurvey,cusTrainingsRequired),cusTrainingsRequired,grp_TrainingsRequired),NumberOfUsers,CountRows(ThisRecord.grp_TrainingsRequired)),label,Concatenate(cusTrainingsRequired," (",NumberOfUsers,")")));

Here,

• ‘Employee Satisfaction Survey’ = SharePoint list name.
• ‘Trainings Required’ = SharePoint list yes/no field name.
• Using the AddColumns() function, we created the cusTrainingsRequired column that stores true or false values of the Training required field in the form of Yes or No.
• ColEmpSatisfactionSurvey = Collection containing the SharePoint list details and the above-created custom field.

We created the second collection [colEmpTrainings] to display the training required field survey details on the line chart.

• ShowColumns() = Used to display only the cusTrainingsRequired field on the collection.
• GroupBy() = Groups the Yes value and no value records from the collection and stored in the grp_TrainingsRequired field.
• AddColumns() = Creates a custom column [NumberOfUsers ], which stores the count of Yes value records and No value records using the CountRows() function.
• Another AddColumns() function creates the label column that stores the concatenation of Yes and No values with the count of employees who have chosen these values.

2. Add a line chart from the + Insert tab. Please provide the below code on its items property:

colEmpTrainings

Here, the colEmpTrainings collection name contains the code to display the number of employees who choose yes/ no values for the training required.

3. To display the values on the line chart like below, add true value to the given property of the Line Chart.

ShowValues =true

4. To display the x and y-axis labels on a line chart. Open the Properties of the line chart-> Change Grid style according to your requirements, like in the example below.

5. Also, open the Advanced properties of the line chart and check the Label; the Series took the column names below.

Label = label
Series =NumberOfUsers

4. Provide the code below on the items property of the Legend in the line chart.

colEmpTraings.label

The above fetches the label values into the legend control in the composite of the line chart.

Save changes and publish the app. Before previewing, run the OnStart property of the App object.

5. Instead of getting the number of employees chosen yes/no values, we can also display the percentages. To get percentages, update the second collection with the code below in the OnStart property.

ClearCollect(colEmpTraings,AddColumns(AddColumns(GroupBy(ShowColumns(ColEmpSatisfactionSurvey,cusTrainingsRequired),cusTrainingsRequired,grp_TrainingsRequired),NumberOfUsers,RoundUp((CountRows(ThisRecord.grp_TrainingsRequired)/CountRows(ColEmpSatisfactionSurvey))*100,0)),label,Concatenate(cusTrainingsRequired," (",NumberOfUsers," %)")));

In the above code, I additionally added the RoundUp() function.

• RoundUp((CountRows(ThisRecord.grp_TrainingsRequired)/CountRows(ColEmpSatisfactionSurvey))*100,0))
• CountRows(ThisRecord.grp_TrainingsRequired) = CountRows() function returns the count of yes and no fields from the current collection.
• CountRows(ColEmpSatisfactionSurvey) = Returns the total row count in the collection that contains SharePoint list items.
• Then, for the division result of the above two codes, we’re multiplying by 100 to get a percentage.
• If we get decimal values to take round figure numbers, we use the RoundUp() function.

Save the changes and preview the app once. You will see the percentage of employees who have chosen yes or no for the training needed question.

I hope you understand creating a Power Apps line chart with the SharePoint list yes/no field. I also explained how to calculate percentages and display them with the label in a line chart. Along with these, true and false values are converted into yes or no. You can follow this article if you’re trying to create a line chart to display the number of users who choose yes or no values for the SharePoint list Yes/No field.

Also, you may like:

• Create Power Apps Pie Chart From SharePoint Choice Column
• Set Default Value in Power Apps Modern Radio Button Control
• Filter Power Apps Line Chart Based On Date Range?
• Display SharePoint List in Power Apps – 5 Various Ways
• Power Apps Naming Conventions [Standards & Guidelines]
• Dynamically Display Image in Power Apps
• 9 Useful Power Apps Form Validations Examples