How to create your own Project Management Planner in Power BI

Submission by Ken Ong https://www.linkedin.com/in/kenneth-ong/

As a business analyst, I am tasked with helping manage the documentation and execution of various aspects of a project. I quickly noticed that many teams have very useful information contained in disparate locations. While crucial on their own, being able to combine and integrate this information would allow someone to get even more benefit from this data.

In this article, I’ll walk you through the steps to create your very own sleek and visual project management dashboard on PowerBI to help you quickly gauge the progress of your projects.

To demonstrate how easy this could be to set up for any team, I will show you how to pull data directly from a shareable Google Sheet, which can be easily updated and shared without the need for any specialized project management software.

Picking Your Data

The first step is to pick the data that you want to display in the dashboard. You can find the sample data here to follow along with.

The key information that this dashboard uses are:

1. A list of tasks with due dates and the person assigned to the task
2. A list of people and their respective roles in the project
3. A risk log

For this dashboard, I also added an “assignment” tab which organizes the tasks into distinct projects/assignments. This can be used to further filter the dashboard.

Linking your Data

The first step is to link your google sheets data to PowerBI. Credit goes to NateChamberlain.com for providing the steps on how to do this.

The first step is to create your google sheet, enter your data, and then make sure your sheet can be viewed (or edited) by anyone with the link

Once you have the link, you need to edit it as follows (We will use my link as an example)

• https://docs.google.com/spreadsheets/d/1QiuZD_AvW4wINMI9o2ifLekzxtil36C1ImPBpjld8yA/edit?usp=sharing

First, remove the “/edit?usp=sharing” from the URL and add “/export?format=xlsx&id=” in its place

• https://docs.google.com/spreadsheets/d/1QiuZD_AvW4wINMI9o2ifLekzxtil36C1ImPBpjld8yA/export?format=xlsx&id=

Then, copy the sheet ID (found between “/d/” and “/export” and add it to the end.

• https://docs.google.com/spreadsheets/d/1QiuZD_AvW4wINMI9o2ifLekzxtil36C1ImPBpjld8yA/export?format=xlsx&id=1QiuZD_AvW4wINMI9o2ifLekzxtil36C1ImPBpjld8yA
• If you click on this link, it downloads the Google Sheet as an Excel file, which can be read in PowerBI.

Once you have this updated link, go to PowerBI. and select the “Web” data source. Enter the edited URL and press “OK”

Select the tables that you want to import, and press “Transform Data”. Make sure that your data is formatted properly.

In this case, I made sure to:

• Promote the first row to a header
• Changed the data type of the “task start date” to a date format
• Changed the data type of the “Percent Completed” to a percent format

Now that your data is linked, you can edit your Google Sheet directly. Refreshing your PowerBI report will cause this to reflect in your dashboard

Setting up your Data Model

The following tables can be linked together:

• Assignments[Assignment Name] and Tasks[Assignment]
• Resources[Name] and Tasks[Primary Resource Name]

Creating your Report

GANTT Chart

The Gantt Chart is the real star of the show and is used to show the timeline and person responsible for each task.

The Gantt chart being us is the custom Gantt visual (Version 2.2.3 is used here). This is the first result when you search “Gantt” in the “add more visuals” option.

Once this visual is imported, use the data from your Google Sheets to populate the Gantt Chart. In my example, the following data was mapped in the following manner:

• Note: the fields are to the left, and the entered columns are on the right

Legend: Resources[Role]

Task: Task[Task Name]

Start Date: Task[Task Start Date]

Duration: Task[Task Duration (Days)]

% Completion: Task[Percent Complete]

Resource: Resources[Name]

• Note: There is also an option to add Milestones if needed for your project

Once populated, the Gantt Chart is complete!

Additional Visuals

The following features/visuals are also present on this dashboard.

Filter by Assignment:

This is used to filter the entire dashboard. The original data relates each task to a specific assignment/sub-project, allowing you to view the report for a specific set of assignments.

This utilizes the Task[Assignment] column in the Slicer visual

Assignment Completion Table

This is used to show the completion percentage of each assignment. This utilizes the Table visual with data from the Assignments[Assignment] and Assignments[Percent Complete] Columns

The data bars are created by using conditional formatting. Set the Maximum to 1 (100%).

Task Table:

This is used to show the descriptions and details of each task. This utilizes a Table visual, and can have any task information you need.

Risk Log

This pulls information the Risk table and conditionally formats each item depending on its risk level.

First, set up a Table visual with the columns Risk[Description] and Risk[Risk Level]. Then, use conditional formatting to color each field depending on its field value. This requires the actual color code to be associated with a respective risk level.

In the risk table in PowerBI, you need a new column (Named “First Hex_Color”) that inputs a color depending on the risk level. This can be done by the following DAX formula:

Hex_Color = IF(Risk[Risk Level] = “Low”,”#90EE90",

IF(Risk[Risk Level] = “Medium”,”#FFFF99",

IF(Risk[Risk Level] = “High”,”#FFCCCB”)))

This looks at the risk level and returns #FFCCCB (Red) for high risk, #FFFF99 (yellow) for medium risk, and 90EE90 (green) for Low risk.

• You can change the HEX code to change the color as needed

Stay tuned for more data driven tips with Power BI