Power Apps Canvas App Dashboard [Using Charts]

While building applications in Power Apps, we are often required to create a dashboard so that admin people can easily manage all the screens and their functionality in one place. I recently developed an employee satisfaction survey application that requires a simple dashboard.

So, in this article, I will explain everything about the Power Apps Canvas App Dashboard and how to create a simple dashboard in the Power Apps Canvas app step by step.

Track Your Progress With Dashboards and Charts in Power Apps

Look at the example below; the top section displays the number of employees who submitted surveys based on today, this week, this month, and last. So that admin people can easily track the number of surveys submitted.

Also, the pie chart represents the job satisfaction percentages, and the data table filters the employee’s details based on the pie chart values we selected.

Here is the SharePoint list containing the details of the employee satisfaction survey.

The table below contains the SharePoint list column names along with their data type:

Column Name	Data Type
Medical Benefits	Title
Email	Single line of text
Phone Number	Single line of text
Department	Choice (IT, HR, Finance, Marketing, Sales)
Job Satisfaction	Choice(Very satisfied, Somewhat satisfied, Neutral, Somewhat dissatisfied, Very dissatisfied)
Is Job Challenging?	Yes/No
How Happy Are You At Work?	Number
Medical Benifites	Choice(Very Poor, Poor, Average, Good, Excellent)
Survey Submitted Date	Date& Time

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

We’re creating a collection to avoid Delegation warnings.

ClearCollect(colEmpSatisfactionSurvey,'Employee Satisfaction Survey');

Here, the ClearCollect() function creates the collection from the SharePoint list.

• colEmpSatisfactionSurvey = collection name.
• ‘Employee Satisfaction Survey’ = SharePoint list name.

Save changes and Run the OnStart property once to create the collection.

2. Add a new screen for the dashboard. Within that, add the following controls.

• Container
  • Text labels for :
    • Total Registers
    • Today Registers
    • This week Registers
    • Last Week Registers
    • This Month Registers
    • Last Month Registers

3. Provide the formulas below on the Text property of each text label within the container.

Total:

"Total: "&CountRows(colEmpSatisfactionSurvey)

Here, we’re concatenating the “Total: “ text with the total count of records from the collection with & operator.

CountRows() function returns the number of records present in the collection.

Today:

"Today: "&CountRows(Filter(colEmpSatisfactionSurvey,'Survey Submitted Date'>= Today()))

Here, the Filter() function filters the records when the ‘Survey Submitted Date’ value matches today or is greater than today’s date. CountRows function gets the count of the filter function returns data.

This Week:

"This Week: "&CountRows(Filter(colEmpSatisfactionSurvey,'Survey Submitted Date' >= DateAdd(Today(),-Weekday(Today(), StartOfWeek.Monday) +1 )&& 'Survey Submitted Date' <= DateAdd(Today(), 5-Weekday(Today(),StartOfWeek.Monday)+1)))

Here, the Weekday() function returns the weekday number of the provided date. So if the ‘Survey Submitted Date’ values are present between Saturday and Monday, it is considered this week.

Last Week:

"Last Week: "&CountRows(Filter(colEmpSatisfactionSurvey,'Survey Submitted Date' >= DateAdd(Today(), -7 - Weekday(Today(), StartOfWeek.Monday) + 1)&& 'Survey Submitted Date' <=  DateAdd(Today(), -Weekday(Today(), StartOfWeek.Monday) + 1)))

Here:

• DateAdd(Today(), -7 – Weekday(Today(), StartOfWeek.Monday) + 1) = Returns the last week Monday date.
• DateAdd(Today(), -Weekday(Today(), StartOfWeek.Monday) + 1) = Returns this week Monday date.
• Using the filter function, we’re comparing the records present in between

This Month:

"This Month: "&CountRows(Filter(colEmpSatisfactionSurvey,Year('Survey Submitted Date') = Year(Today()) && Month('Survey Submitted Date') = Month(Today())))

Here,

• Year() = Returns the year of the provided date.
• Month() = Returns the month of the provided date.
• So, the above formula compares the survey submitted dates of record with the current month and year of today’s date.

Last Month:

"Last Month: "&CountRows(Filter(colEmpSatisfactionSurvey,Year('Survey Submitted Date') = Year(Today()) && Month('Survey Submitted Date') = Month(Today())-1 ||(Year('Survey Submitted Date') = Year(Today()) - 1 && Month(Today()) = 1 && Month('Survey Submitted Date') = 12)))

The above formula checks:

• Month(Today())-1 = Returns last month number.
• Year(Today()) – 1 && Month(Today()) = 1 && Month(‘Survey Submitted Date’) = 12)) = This formula we used to fetch the Dec (12)month details because when we’re in Jan (1) month, the last month will be Dec but this Month(Today())-1 returns 0. To handle December month records on last month’s calculations, we used this code.

4. Now, to display the employee’s feedback on job satisfaction in percentage on the Power Apps pie chart, add the Pie Chart from the +Insert tab. Then, provide the code below on its items property.

AddColumns(
    AddColumns(
        GroupBy(
            AddColumns(
                ShowColumns(
                    colEmpSatisfactionSurvey,
                    'Job Satisfaction'
                ),
                JobSatisfactionValue,
                JobSatisfaction.Value
            ),
            JobSatisfactionValue,
            grp_JobSatisfaction
        ),
        NumberOfUsers,
     CountRows(ThisRecord.grp_JobSatisfaction)
    ),
    label,
    Concatenate(
        JobSatisfactionValue,
        " ( ",
        NumberOfUsers,
        ")"
    )
)

In the above code, we used the Add columns() function to create the custom column to store the job satisfaction choice values:

• colEmpSatisfactionSurvey = Collection name.
• JobSatisfactionValue = Custom column stores the SharePoint lists job satisfaction choice column values.
• To get the number of employees who choose feedback on job satisfaction choice values, we used the GroupBy() function that creates the grp_JobSatisfaction column.
• The NumberOfUsers column stores the number of employees who choose each job satisfaction value.
• The label column stores the combination of job satisfaction value and the number of employees.

5. Open the Advance Properties of PieChart; Label and Serial Numbers properties are under the items property. For those properties, choose the label and NumberOfUsers columns from the dropdown.

6. To display the employee’s details when we click on the job satisfaction values on the pie chart, add a Data Table control from the +Insert tab.

Provide the below code in its Items property:

Filter('Employee Satisfaction Survey','Job Satisfaction'.Value=PieChart1.Selected.JobSatisfactionValue || PieChart1.Selected.JobSatisfactionValue=Blank())

Here, the filter function filters the items in the SharePoint list that match the pie chart selected job satisfaction value. If we don’t select any item, then by default, it will display all employees’ details.

Now, save the changes and publish the app. While previewing, click on the job satisfaction values to see the details of the employees who submitted the feedback.

I hope you understand how to create a simple dashboard in Power Apps applications. In this article, I explained how to get the sharePoint list items based on the periods, such as today, this week, this month, etc. Also, displaying the number of employees who choose each job satisfaction value and displays those employee’s details on the table control.

Also, you may like:

• Create Power Apps Pie Chart From SharePoint Choice Column
• Power Apps Line Chart With SharePoint Yes/No Column
• Create Status Indicator Using Power Apps
• Create a Power Apps Canvas App From a Template
• Display SharePoint List in Power Apps – 5 Various Ways
• Dynamically Display Image in Power Apps