Creating Gantt Charts for Project Management Using Power BI
One thing that is expected of every project manager is knowing, using, and presenting a Gantt Chart for the project(s) they manage.
What is a Gantt Chart?
This is a very specific type of bar chart used in project management for the purpose of showing the progress of different tasks, how much work is done as time progresses, and what we have accomplished. For more information, you can check this out: https://en.wikipedia.org/wiki/Gantt_chart
There are several ways to create a Gantt Chart for a project, and you can visualize this data using many different tools that are readily available or you can even write your own programs to do this for you. Today, though, let us learn how to create and visualize a Gantt chart using Power BI.
Why Use Power BI?
Personally, I tried writing my own programs and ended up getting endless errors over silly little complications or I was not happy with the quality of the output I got. I have not tried using any project management tools or tools that are developed for purposes specifically like this yet, but as someone using Power BI often, I wanted to give this a try. And the output looks good, which is why I decided to share this with you all.
The Required Data:
To visualize something, anything, we need some data, right? Since no real-life project data can be shared, as that is confidential, let us use dummy data for this Gantt chart today.
The Information Available in This Data:
Note that in this data visualization project, we will be able to track the progress of several projects at once. This is in the form of a spreadsheet. Like any basic project management dataset, this one would also include “Client name” (optional), and must-have fields like “Project Name”, “Task Name/Sprint No.”, “Starts On” and “Ends On” date values for each of these tasks, role no. 1, 2, 3, … and the no. of tasks assigned to them in each Sprint or level, the number of tasks they have completed at this point, till today, and a final column, where we will use a formula to calculate and save the total work done:
Total work done = ((No. of Tasks Finished by project member 1 + 2 + 3 + …)/(No. of Tasks Assigned to project member 1 + 2 + 3 + …)) * 100
Yes, this will give us a percentage, a percentage of the total work done in each sprint or level of the project, which is what we want. The data table would look something like this:
As you can see, there are 6 projects here. All those cells where project tasks have not yet been assigned to members will show “0” most likely because we have not come to that sprint/level of the project yet or because the one managing this data has not yet updated this sheet.
And this will result in an error when you have to calculate the percentage because anything divided by 0 will show an error.
Using Power BI for Visualizing Sprint Trackers and Project Progress for Different Projects:
Open Power BI and get this data from your source, but remember that Power BI allows you to transform your data before you load it. Because of the error mentioned above, you will be notified that n rows have errors. We will use this “Transform” option to make sure that this error is cleared. When you choose the “Transform” option after loading the data, then click on the “Transform Data” option under “Home”, a new window will open, and you will see a range of options under “Transform”.
Transforming Data (Percentage Value) in Power BI:
For our case, it is the final column, the “percentage” column that needs to be transformed, so click the header of the last column, and first, go to “Replace Values” to “Replace Errors” and because tasks have not even been assigned yet, let us replace all “error” values with “0”. Next, go to the top right side of the ribbon, click “Rounding” and round these off to preferably 2 or 3 digits. Once you apply these changes, as a result, instantly, you will see all “Error” values changed to “0” and unnecessarily long numbers like “85.71429” rounded to simpler values like “85.71”.
Downloading Gantt Chart in Power BI:
In Power BI, you don’t get the “Gantt chart” visualization downloaded by default. You will have to install this yourself. There are actually many widgets and Gantt Chart options created by several other brands, but the Gantt Chart option provided by Microsoft is free to use, so download that.
You can do this by clicking “Get More Visuals” (the three dots) below the existing options under the “Visualizations” section.
Creating Your Own Gantt Chart to Show Project Progress on Power BI:
Once installed, click that figure, and an empty Gantt chart will appear on your screen. Now, it is time to fill the values in empty boxes in the “Visualizations” field. The “Task” in our case will be “Sprint No.”, the “Parent” will be “Project Name”, and enter the “Start Date” and “End Date” with their respective columns. This is easy: you have to drag the values from the “Data” pane and drop them in these boxes.
After following the above steps, you will get a basic horizontal bar graph, which will be of good quality. As you have multiple projects, you can switch between different projects or see the status of them all at once.
Showing the Progress or Tracking Sprint Progress on Gantt Chart in Power BI:
But note that this does not show the progress and does not allow you to track the progress of any sprint in any project. It should be as simple as just entering the percentage value in the “% completion” section, right? Yes, ideally, it should be, but for some reason, I kept getting errors that I could not resolve.
Finally, when I chose to alter the value and chose “Average” instead of “Count”, I got the desired result.
And “Tooltips” are additional information you want to be seen when you hover over an area in the graph. I chose to enter three different roles, but you enter other values, but I would suggest that you use this column only for displaying important information.
Nobody likes a much-loaded visualization.
Also, the “Tooltips” allows you to enter only a maximum of 3 values and not more. Could this be for the reason mentioned above? But this is a certain limitation, and you can’t do anything about that in the version of Power BI we have now.
You can also try using the “Clustered Bar Chart” for this instead of the Gantt chart (which might not be available for free memberships). The results convey the same information but are shown differently as you can see —
There are a few other limitations to using Power BI for project management tasks, but for a simple, basic, good-quality visualization that is also iteractive and dynamic, Power BI works quite well! What do you think?
