Venturing Into Data Mastery: Unveiling a Tale of Rediscovery and Crafting a Dynamic Project Management Dashboard in Tableau

14 min readDec 2, 2023

Puff! This is me blowing off steam. It’s been a while since I wrote a Medium post, but here we are, all thanks to my mentor, Dzifa Amexo.

In February this year, Saverio Riochetti another one of my mentors accidentally sent me an “Anticipate Data Super Star competition” on Twitter (now X). Eager to enhance my data visualization skills — one of my goals for the year — I embraced the opportunity. When the time came, I looked through the dataset and this Project Management dataset caught my eye (link), I couldn’t resist the challenge, so, I decided to try my hands on it.

Please sit back, grab a cup of whatever you like and let’s dive into my Project Management Dashboard creation process as the data storytelling unfolds.

A little insight about the data…

This dataset contains 11 columns and 161 rows of data. The time range of this dataset is between June 2019 to February 2022. Below is a brief description of the columns in the dataset:

End Date: The anticipated conclusion of the project, marking the completion of all tasks and objectives.
Start Date: The initiation point, indicating when the project officially begins, and activities are set into motion.
Status Date: A crucial checkpoint in time that reflects the current state of the project, offering insights into progress and potential adjustments in terms of project risk.
Project: The initiative or venture that brings together tasks, resources, and goals under a unified framework.
Project Manager: The individual responsible for steering the project, overseeing its progress, and ensuring alignment with objectives.
Project Category: The classification that places the project within a specific group or type, providing context for its nature and purpose.
Project Risk: Identification of potential challenges or uncertainties that might impact the project’s success, prompting proactive mitigation strategies. This changes with time.
Project Priority: The relative importance or urgency assigned to a project, guiding resource allocation and attention.
Budget: The allocated financial resources earmarked for the successful completion of the project.
Consumed Budget: The portion of the budget that has been utilized or spent at a given point in the project timeline.
Progress %: A quantitative measure indicating the degree of completion achieved, offering a snapshot of the project’s advancement relative to its goals.

This data contains the key elements that serve as the backbone for the successful execution of projects in the realm of project management, I guess you can see why it caught my attention. It is portfolio-worthy.

Small talks
This is November 2023 and I have spent the last 4 weeks reviewing this visualization alongside my mentors Dzifa and Saverio. Their feedback has been my greatest source of improvement. Let’s break this article into two - A Tale of Rediscovery and Crafting a Dynamic Project Management Dashboard in Tableau. Stay with me.

Crafting a Dynamic Project Management Dashboard in Tableau

Earlier this year, I was more concerned about how my dashboard appeared, so, my first line of action was to go search for different Project Management dashboards on Tableau Public, believe me, I saw a diverse array of inspiring designs. After sifting through these visualizations, I selected a handful of captivating dashboards that caught my eye and looked interesting enough to recreate. I took my time to examine these chosen dashboards and extracted various elements and chart styles that intrigued me. I challenged myself to the not-so-regular charts. After all, you learn to do by doing.

Once my chart decisions were made, I entered the analytical and chart-building phase. This phase was fully experimental for me because I created certain charts for the first time. I started with bar charts and created at least three (3) distinct types of bar charts: rounded progress bar charts, lollipop bar charts, rounded bar charts and a Gantt chart.

(PS: for extensive knowledge on how to create these charts, kindly click the words)

The Idea

Crafting the wireframe for this dashboard in Canva was a tasking process that demanded considerable thought and lots of deliberations with myself. Every aspect, from the layout to the selection of the colour palette, was a tough decision-making process for me.

I imported my background image and the dashboard building started. Since I had drawn the dashboard prototype and knew where each chart would go, I would say the act of arranging the charts on the background image felt straightforward. Knowing the precise placement of each chart made this phase remarkably effortless.

The How

Section 1 — Overview

This part gives the overall position of the Project Managers, their respective projects, allocated resources and utilization details. It encompasses:

Total Projects: The overall count of projects within the system.
Total Project Managers: The count of individuals managing projects.
Budget Allocated: The total financial resources designated for project implementation.
Budget Utilized: The amount of allocated budget that has been utilized or spent.
Project Managers and Projects Handled: A breakdown showcasing each Project Manager and the number of projects under their management.
Project Category and Category KPI cards: This categorization facilitates drilling down into specific project categories, unveiling projects falling under each category.

This view also delves deeper into financial and progress details:

Total Budget Allocated and Utilized per Project Manager: Offers insights into the budget distribution among managers and how much has been utilized, aiding in resource management.
Progress Completion per Project using a Bubble Chart: Visual representation showcasing project progress, allowing for quick comprehension of each project’s status.
Project Priority and Risk Analysis: Showcasing the total budget allocated and utilized per project priority, offering insights into financial distribution across priority levels. Additionally, it provides the number of risks identified within each priority category, enabling a comprehensive risk assessment.

The majority of these insights were generated using Tableau functions such as SUM(), COUNT(), and COUNTD(), streamlining the data analysis process and deriving key metrics efficiently.

Total Project Managers per Category: Utilizing custom shapes on the marks card, this section reveals the total count of project managers within each category. This visual representation provides a quick overview of manager distribution across various project categories.

In addition to this, there’s an interactive functionality, clicking on a specific project manager directs users to a dedicated details tab, offering a comprehensive rundown of the activities undertaken by that particular project manager.

Section 2 — Details

This section details crucial aspects related to Project Managers and project specifics:

Project Manager Information: Displays the name of the selected Project Manager, accompanied by the count of projects they have/are currently handling and the respective project categories.
Project Progress and Financials: Includes project completion progress, the allocated budget, and the actual budget utilized, providing a snapshot of project advancement and financial resource management.
Project Duration: Highlights the start and end dates of each project, along with the remaining days until the project end date calculated from the most recent status date. This information offers a clear perspective on project timelines and deadlines.

Furthermore, it illustrates the evolution of progress and budget:

Budget Evolution and Utilization: Illustrates the evolution of budget allocation, showcasing budget utilization and the percentage consumed at different status dates.

This shows the creation process for the Budget Evolution chart involved the strategic utilization of AVG(7) pill to generate the boxes representing the percentage of the consumed budget. The AVG(7) axis was reversed in other to keep the boxes above the bars. Other crucial details, essential for the chart’s accuracy and readability, were strategically placed in designated sections on the Marks card (see the picture above). This arrangement ensured that important information such as labels, colours, and details were correctly attributed to the respective data points, enabling a clear and insightful representation of the budget evolution.

Progress Evolution Over Time: Tracks the progress evolution over different status dates, showcasing the time spent and percentage progress achieved at each status date. This is filtered on Project.
Project Priority and Risk Analysis: it provides the number of risks identified within each priority category, enabling a comprehensive risk assessment.

Just in
I stumbled upon a nifty trick that involves placing a blank dashboard object strategically over elements, controlling what users can click on — especially elements without tooltips or those not necessary for interaction. I gave it a try here, and it worked like a charm, granting me control over clickable areas. Give it a shot and share your experience in the comments!

Section 3 — Details and Timeline

Details View: presents a focused view of all the project attributes, showcasing project manager names, categories, progress percentages, budget details, start and end dates, and other crucial project information.

Timeline View: accentuates essential aspects such as project start and end dates per project with precision and clarity.

This hands-on exploration not only honed my skills but also laid the groundwork for the unique visual elements that would come to define my project management dashboard. Beyond skill enhancement, it became the cornerstone of my analytical growth, shaping the way I approach data visualization and analysis.

A Tale of Rediscovery

Dzifa and I embarked on a feedback journey, starting with this visualization. Despite working on the feedback diligently, a sense of unease lingered, something wasn’t just right. This marked the beginning of a profound discovery.

In an effort to pinpoint the issue, I reached out to Saverio. Days were spent going over so many things trying to decipher the problem but I couldn’t communicate my misgivings properly.

Saverio’s advice echoed in my mind: “Play around with the Excel file”.

Following this counsel, I delved into the data. As I prepared to meet with Dzifa again, a breakthrough emerged. Finally, I could communicate my concerns clearly. It was a moment of truth — I had to acknowledge, “Ayo, this is seemingly good but outrightly misleading.”

The Problem

Take a look at the preview of the dataset, can you pinpoint it?

Certain elements remain constant within the dataset, they are End Date, Start Date, Project, Project Category, Project Manager and Project Priority. However, the other fields fluctuate based on the Status Date, leading to duplication of the supposed static fields.

Just take a look at this, this is a staggering leap — 161 projects instead of 19! The discrepancy showcases a whopping 747% increase, revealing the misleading representations. Imagine a 747% increase in Budget, Budget utilized, and duration amongst others.

The Solution

Now that I have been able to pinpoint the problem, the next call of action is to find the solution which I did. This solution involved harnessing the power of Level of Details (LODs) expression, specifically utilizing Fixed LOD to address the challenge at hand.

LOD expression syntax: A level of detail expression has the following structure:

{[FIXED | INCLUDE | EXCLUDE] <dimension declaration > : <aggregate expression>}

I started by organizing the fields into folders by grouping related fields together. This approach ensured a clearer, more field-oriented workflow thereby reducing confusion and enhancing efficiency.

Budget

Let’s break down the code

{} denotes the use of Tableau’s Level of Detail (LOD) expression, allowing for calculations at different levels of granularity in the data.

Within the curly braces {}, the expression `FIXED [Project Manager],[Project]:MAX([Budget])` performs the following actions:

FIXED indicates the LOD expression type, which fixes the calculation at the specified level of detail, here being [Project Manager] and [Project].
This means the calculation is performed for each unique combination of [Project Manager] and [Project].
MAX([Budget]) calculates the maximum value of the [Budget] field within each unique combination of [Project Manager] and [Project].
SUM() is an aggregation function used in Tableau to calculate the sum of a specific expression. This function wraps around this fixed calculation, aggregating the maximum budget values obtained for each [Project Manager] and [Project] combination across the dataset. This is an optional approach and you can replace the aggregation method with your preferred one.

Fixed Budget and Tableau’s default aggregation

By default, Tableau uses the SUM() aggregation unless specified otherwise. This flexibility allows you to select the desired aggregation method for your data.

Consumed Budget

Here’s the breakdown of the calculations:

Fixed CB (Consumed Budget): Calculates the sum of the maximum consumed budget for each unique combination of [Project Manager] and [Project].
Fixed CB per Status Date: Finds the maximum consumed budget for each unique combination of [Project Manager], truncated month from [Status Date], and [Project].
Running CB: Computes the running sum of the consumed budget over the data rows.
%CB fixed: Calculates the percentage of fixed consumed budget against the fixed budget.
%CB fixed per Status Date: Computes the sum of the fixed consumed budget per status date and divides it by the fixed budget.

%CB: Calculates the percentage of the consumed budget relative to the fixed budget.
%CB Only: Computes the percentage of fixed consumed budget against the fixed budget.

Date

The duration calculated field uses the DATEDIFF() function in Tableau calculate the difference in days between the [Start Date] and [End Date]. The result is then converted to an integer using the INT() function, providing the duration in whole days without decimal values.

The max duration calculated field employs LOD calculations to find the maximum [Duration] within distinct groupings of [Project Manager], year from [End Date], and [Project].

On the other hand, the “Fixed Duration Formula” uses another LOD expression to fix the calculation at the level of [Project Manager], disregarding other dimensions. It computes the value of [Max Duration] within each unique [Project Manager] grouping. The sum of the “Max Duration” comes from the Max duration calculated field while this expression isolates that value for each [Project Manager]. See the difference

A gif showing the difference between the Fixed Duration and Max Duration Field

Fixed Duration per Status Date: This calculates the max duration maximum [Duration] within distinct groupings of [Project Manager], year, month from [Status Date], and [Project].

Spent Time: Measures the duration in days between the [Start Date] and [Status Date].
Running Spent Time: Tracks the maximum spent time across rows, showcasing the running maximum of the maximum spent time.
% Spent Time: Computes the percentage of spent time relative to the total fixed duration per status date.
Days Left: Determines the remaining time in days by subtracting the maximum spent time from the maximum duration. The result is an integer value presenting the remaining duration until completion.

% Progress

Here’s the breakdown of the calculations:

Fixed Progress% (Sum): Calculates the sum of the progress percentage for each unique [Project Manager].
Fixed Progress% (Max): Finds the maximum progress percentage for each unique combination of [Project Manager] and [Project].
Fixed %Progress per Status Date: Determines the maximum progress percentage for each unique combination of [Project Manager], truncated month from [Status Date], and [Project].
Running Progress%: Calculates the running sum of the progress percentage over the data rows.
%Progress: Computes the percentage of running progress relative to the sum of the fixed progress percentages.

These formulas replaced the original dataset fields. The goal was to ensure accurate analysis, making it easier to gain clearer insights from the data.

Feel free to interact with the viz here. You can also go into each sheet to see how these new fields were employed. In place of COUNT(), COUNTD() was utilized.

Recall that the Project Category can be filtered by simply clicking any of the Category KPI Cards. The blue highlights indicating a clicked KPI card have been removed, all thanks to Gbolahan Adebayo. You can achieve this by creating a customized shape in PowerPoint, setting the outline and shape fill to no fill, and saving it as an image in the shapes folder under My Tableau Repository. Afterwards, change the marks card to ‘shape’ and select the saved image. Align your text as desired, and voila! For more details, check here.

KPI Card with and without the Blue Highlight

A status date filter was also introduced because project risks fluctuate over time. This filter allows users to observe the evolution of project risks concerning each status date, offering a dynamic view of how risks changed throughout the project’s timeline.

The Project Manager Dashboard now filters projects relevant to the selected manager, enhancing focus. Additionally, a status date filter tracks project risk changes over time. The legend in the progress evolution corner enables a clear understanding of % progress versus spent time. Furthermore, the ‘Days Left’ metric has been rephrased as ‘Last Status Date to End Date’, refining clarity around project timelines. These enhancements offer a more intuitive and insightful dashboard experience.

The Detail Dashboard now includes a reset filter button. Filters are adjusted to display only relevant values, ensuring a more focused and efficient exploration of the data within the dashboard.

Future Path: Insights and Innovations Explored

In this journey of data exploration, visualization and rediscovery, each improvement aimed to give a clearer, more intuitive and easy-to-use dashboard experience. By refining filters, fields, and tooltips, adding dynamic elements, and considering user-centric features, this dashboard evolved into a powerful tool for understanding project dynamics.

I’m amazed at how much I learnt from this project. It’s exactly what I needed for my data goals. These changes show a dedication to not just presenting data in an appealing way, but crafting an accurate interactive narrative that can help make the right business decisions.

I’m eager to continue developing my skills by building complete packaged visualizations. My idea of a complete package viz involves precise analysis paired with stunning visuals that guide better business decisions.

Stay tuned for more! 😉

The door remains open for further refinements, so your feedback is invaluable! Whether it’s about enhancing the dashboard or improving the article, your suggestions mean a lot. Please share your insights in the comments; I’m eager to learn from your input and make this even better!