Pipeline Project Tracker in Power BI

Build an interactive pipeline pigging dashboard with KPIs and DAX measures.

Introduction

30 Second Summary

In the oil and gas industry, pipeline pigging projects generate mountains of data. Field crews, equipment certifications, budgets, and daily progress all live in scattered spreadsheets that nobody can act on quickly.

In this project, you will build an interactive Pipeline Project Tracker Dashboard in Power BI Desktop that transforms raw Excel data into executive-level KPIs, charts, and status tables.

What You'll Build

You'll build an interactive dashboard in Power BI Desktop that tracks pipeline pigging project progress, equipment readiness, and budget performance using sample data you create in Excel.

By the end of this project, you'll have:

  • Sample Excel data structured using a star schema (Projects, Activities, Equipment tables) modeled on realistic pipeline operations.
  • DAX measures that calculate completion %, budget variance, and equipment certification status with conditional color-coding.
  • A working Power BI dashboard with KPI cards, a RAG-status progress table, budget vs. actual charts, and interactive slicers.
  • Secret Mission: Add a Daily Progress Report page that tracks daily crew output and generates a summary card showing average tasks completed per day.

Are there any prerequisites?

Basic familiarity with Excel is all you need. If you can create a table and enter data into cells, you're ready to go.

Before We Start

Before We Start...

Install Power BI Desktop and Set Up Excel

Power BI Desktop is the tool that transforms raw spreadsheet data into interactive visual dashboards. It's free to use locally, and it's the industry standard for project reporting in oil and gas.

In this step, you'll install Power BI Desktop on your Windows machine and prepare the Excel workbook that will hold all of your pipeline project data.

In this step, get ready to:
  • Install Power BI Desktop on your machine.
  • Verify Power BI Desktop opens correctly.
  • Create your Excel workbook with three data sheets.
Download Power BI Desktop

Windows required

Power BI Desktop is a Windows-only application. You need Windows 10 or later to complete this project. The 32-bit version is no longer supported, so make sure you have a 64-bit system.

  • Press the Windows key to open your search bar.
  • Type Power BI Desktop and look at the results.

✔️ I see Power BI Desktop in my results

Power BI Desktop is already installed. Click it to open the application and skip ahead to the verification substep below.

ⓧ I don't see it

You need to install Power BI Desktop. The easiest method is through the Microsoft Store.

  • Press the Windows key, type Microsoft Store, and press Enter to open the Store app.
  • In the Microsoft Store search bar, type Power BI Desktop and press Enter.
  • Click Install on the Power BI Desktop page.
  • Wait for the download and installation to complete. This may take a few minutes depending on your internet speed.
  • Click Open once the installation finishes.

Prefer a direct download?

You can also download PBIDesktopSetup_x64.exe from the Microsoft Download Center. Run the installer and follow the on-screen setup steps.

Installation stuck or failing?

Make sure your Windows version is up to date. Power BI Desktop requires Windows 10 or later. If the Store install hangs, try restarting the Microsoft Store app and attempting the install again.

help me troubleshoot my Power BI Desktop installation

Verify the installation
  • If Power BI Desktop isn't open yet, press the Windows key, type Power BI Desktop, and press Enter to launch it.
  • Close any sign-in prompts that appear. A Microsoft account is not required to use Power BI Desktop locally.
  • Close the tutorial or splash screen if one appears.

You should now see the Power BI Desktop Home screen. The Home ribbon at the top has buttons including Get Data, Transform Data, and New Visual.

What is Power BI Desktop?

Power BI Desktop is a free data visualization tool from Microsoft. It connects to data sources (like Excel files), lets you transform and model the data, and then build interactive reports and dashboards. Publishing to the Power BI service requires a license, but everything you do locally in this project is completely free.

Set up Excel with sample data

Your dashboard needs a data source. You'll create an Excel workbook with three separate sheets, one for each type of data your pipeline tracker will use.

  • Press the Windows key, type Excel, and press Enter to open Microsoft Excel.
  • Click Blank workbook to create a new file.

By default, Excel creates a single sheet called Sheet1. You need three sheets named Projects, Activities, and Equipment.

  • Double-click the Sheet1 tab at the bottom of the screen. Type Projects and press Enter.
  • Click the + icon next to the sheet tab to add a new sheet.
  • Double-click the new Sheet2 tab. Type Activities and press Enter.
  • Click the + icon again to add one more sheet.
  • Double-click the new Sheet3 tab. Type Equipment and press Enter.

Why three separate sheets?

Each sheet represents a different table in your data model. The Projects sheet holds high-level project info. The Activities sheet tracks individual tasks within each project. The Equipment sheet lists the tools and gear assigned to each project.

This separation follows a star schema pattern, where related data lives in separate tables linked by a shared key. Power BI works best with data structured this way.

  • Save the workbook by pressing Ctrl+S.
  • In the Save As dialog, navigate to a folder you can easily find (your Desktop or Documents folder works well).
  • Name the file PipelineTracker.xlsx and click Save.

You should now see three sheet tabs at the bottom of your workbook: Projects, Activities, and Equipment.

Don't see all three tabs?

Make sure you clicked the + icon twice (once for each new sheet). If a tab still says Sheet2 or Sheet3, double-click it again to rename it.

help me set up my Excel workbook with three named sheets

Your tools are ready. Next up, you'll fill those empty sheets with realistic pipeline pigging project data.

Create Your Project Data in Excel

Power BI is ready to go, and your Excel workbook is waiting with three empty sheets. Now you need real data to visualize.

In this step, you'll populate those sheets with realistic pipeline pigging project data structured in a star schema. This means a central Projects table linked to Activities (what gets done) and Equipment (what tools are used).

In this step, get ready to:
  • Build a Projects table with 5 pipeline pigging projects.
  • Build an Activities table with 18 activities at various completion states.
  • Build an Equipment table with 12 items tracking certification status.
Build the Projects table

The Projects table is your top-level dimension table. Each row represents one pipeline pigging project with its budget, timeline, and current status.

  • Switch to the Projects sheet in your PipelineTracker.xlsx workbook.
  • In row 1, enter the following column headers starting from cell A1:
ProjectID | ProjectName | Client | Pipeline | StartDate | EndDate | Budget | Status
  • Enter the following 5 projects starting from row 2. Type each value into its own cell:
P001 | 24-inch Gas Line Pigging - Phase 1 | Petronas Gas | 24-inch Gas Export | 2024-01-15 | 2024-03-30 | 120000 | Active
P002 | 16-inch Crude Export Cleaning Run | Shell Malaysia | 16-inch Crude Export | 2024-02-01 | 2024-04-15 | 85000 | Active
P003 | 20-inch Water Injection Pigging | PETRONAS Carigali | 20-inch Water Injection | 2023-11-01 | 2024-01-31 | 95000 | Completed
P004 | 12-inch Gas Lift Inspection Run | ExxonMobil | 12-inch Gas Lift | 2024-03-01 | 2024-05-30 | 50000 | On Hold
P005 | 30-inch Trunk Line Integrity Run | Petroliam Nasional | 30-inch Trunk Line | 2024-04-01 | 2024-07-31 | 200000 | Active

What does this data represent?

Each row is a real-world pipeline pigging project. ProjectID is the unique key that links this table to Activities and Equipment.

The Status column uses three values: Active, Completed, and On Hold. Later, you'll build slicers that let you filter the entire dashboard by these statuses.

  • Select any cell inside your data (e.g., cell A1).
  • Press Ctrl+T (Windows) or Cmd+T (macOS) to open the Create Table dialog.
  • Make sure My table has headers is checked, then click OK.

Why format as an Excel Table?

Power BI reliably detects Excel Tables (the formatted kind, not just plain data in cells). Formatting as a Table ensures your column headers and rows import cleanly into Power BI without manual cleanup.

  • Confirm your data now has alternating row colors and filter arrows on each column header.
  • Save the file by pressing Ctrl+S (Windows) or Cmd+S (macOS).

Don't see the alternating colors?

Make sure you selected a cell inside your data before pressing Ctrl+T. If you selected an empty cell outside the data range, Excel may have created an empty table.

If the dialog shows the wrong range, click Cancel, select all your data from A1 to H6, and try Ctrl+T again.

Why isn't my Excel data formatting as a table?

Build the Activities table

The Activities table is your fact table. It holds the day-to-day tasks that make up each project, along with their planned vs. actual dates and completion percentages.

  • Switch to the Activities sheet.
  • In row 1, enter the following column headers starting from cell A1:
ActivityID | ProjectID | ActivityName | Phase | PlannedStart | PlannedEnd | ActualStart | ActualEnd | PercentComplete | ActualCost
  • Enter the following 18 activities starting from row 2. Leave ActualStart and ActualEnd blank for activities that haven't started yet:
A001 | P001 | Site mobilization | Mobilization | 2024-01-15 | 2024-01-20 | 2024-01-15 | 2024-01-20 | 1 | 8000
A002 | P001 | Launcher rig-up | Setup | 2024-01-21 | 2024-01-25 | 2024-01-21 | 2024-01-26 | 1 | 12000
A003 | P001 | Gauge pig run | Pigging | 2024-01-26 | 2024-02-02 | 2024-01-27 | 2024-02-03 | 1 | 15000
A004 | P001 | BIDI brush assembly | Pigging | 2024-02-03 | 2024-02-10 | 2024-02-04 | | 0.7 | 18000
A005 | P001 | Receiver rig-down | Demobilization | 2024-02-11 | 2024-02-15 | | | 0 | 0
A006 | P002 | Site mobilization | Mobilization | 2024-02-01 | 2024-02-05 | 2024-02-01 | 2024-02-05 | 1 | 6000
A007 | P002 | Pre-pigging inspection | Inspection | 2024-02-06 | 2024-02-10 | 2024-02-06 | 2024-02-11 | 1 | 9000
A008 | P002 | Launcher rig-up | Setup | 2024-02-11 | 2024-02-15 | 2024-02-12 | | 0.6 | 10000
A009 | P002 | Cleaning pig run | Pigging | 2024-02-16 | 2024-02-23 | | | 0 | 0
A010 | P002 | Demobilization | Demobilization | 2024-02-24 | 2024-02-28 | | | 0 | 0
A011 | P003 | Site mobilization | Mobilization | 2023-11-01 | 2023-11-05 | 2023-11-01 | 2023-11-04 | 1 | 7000
A012 | P003 | Launcher rig-up | Setup | 2023-11-06 | 2023-11-10 | 2023-11-05 | 2023-11-10 | 1 | 11000
A013 | P003 | Gauge pig run | Pigging | 2023-11-11 | 2023-11-18 | 2023-11-11 | 2023-11-17 | 1 | 14000
A014 | P003 | BIDI brush run | Pigging | 2023-11-19 | 2023-11-26 | 2023-11-18 | 2023-11-25 | 1 | 16000
A015 | P003 | Receiver rig-down | Demobilization | 2023-11-27 | 2023-12-01 | 2023-11-26 | 2023-11-30 | 1 | 8000
A016 | P003 | Final report submission | Closeout | 2023-12-02 | 2023-12-10 | 2023-12-01 | 2023-12-08 | 1 | 5000
A017 | P004 | Site mobilization | Mobilization | 2024-03-01 | 2024-03-05 | 2024-03-01 | 2024-03-05 | 1 | 5000
A018 | P004 | Pre-inspection survey | Inspection | 2024-03-06 | 2024-03-12 | 2024-03-06 | | 0.4 | 7000

Why use decimals for PercentComplete?

Store completion as a decimal between 0 and 1 (not 0 to 100). A value of 1 means 100% complete, 0.7 means 70%, and 0 means not started.

This makes the DAX formulas you'll write in Step 4 much simpler. DIVIDE works naturally with decimals, so you won't need to divide by 100 everywhere.

Leave cells blank, don't type zero

For activities that haven't started, leave the ActualStart and ActualEnd cells empty (blank). Don't type 0 or N/A. Blank cells import into Power BI as null values, which behave correctly in date calculations and filters.

  • Select any cell inside your Activities data.
  • Press Ctrl+T (Windows) or Cmd+T (macOS).
  • Confirm My table has headers is checked, then click OK.
  • Save the file with Ctrl+S (Windows) or Cmd+S (macOS).

You should see alternating row colors and filter arrows across all 10 column headers.

Seeing dates display as numbers?

If your date columns show numbers like 45306 instead of 2024-01-15, Excel is displaying the underlying serial number. Select the date columns, right-click, choose Format Cells, pick Date, and select a date format.

Help me fix date formatting in my Excel Activities table.

Build the Equipment table

The Equipment table tracks what tools and assets are assigned to each project, along with their certification expiry dates. This lets you spot equipment that needs recertification before it can be used on-site.

  • Switch to the Equipment sheet.
  • In row 1, enter the following column headers starting from cell A1:
EquipmentID | ProjectID | EquipmentName | Category | CertExpiry | Status
  • Enter the following 12 equipment items starting from row 2:
E001 | P001 | Pig launcher 24-inch | Launcher/Receiver | 2024-06-30 | Certified
E002 | P001 | High-pressure hose 2-inch | Hoses | 2024-08-15 | Certified
E003 | P001 | BIDI brush 24-inch | Pigs | 2024-05-20 | Certified
E004 | P001 | Tri-pump unit | Pumps | 2024-02-28 | Expired
E005 | P002 | Pig launcher 16-inch | Launcher/Receiver | 2024-07-15 | Certified
E006 | P002 | Cleaning pig 16-inch | Pigs | 2024-03-15 | Expiring Soon
E007 | P002 | Pressure gauge calibrated | Instruments | 2024-01-31 | Expired
E008 | P003 | Pig launcher 20-inch | Launcher/Receiver | 2024-09-30 | Certified
E009 | P003 | BIDI brush 20-inch | Pigs | 2024-04-30 | Certified
E010 | P004 | Pig launcher 12-inch | Launcher/Receiver | 2024-10-31 | Certified
E011 | P004 | Flow meter | Instruments | 2024-03-10 | Expiring Soon
E012 | P005 | Pig launcher 30-inch | Launcher/Receiver | 2024-12-31 | Certified

What do the Status values mean?

Equipment uses three statuses that map to certification readiness:

  • Certified means the equipment's certification is current and valid.
  • Expiring Soon means the certification expires within the next 30 days.
  • Expired means the certification has lapsed and the equipment cannot be deployed until recertified.

In Step 5, you'll build a donut chart that gives you an instant visual breakdown of how many items fall into each category.

  • Select any cell inside your Equipment data.
  • Press Ctrl+T (Windows) or Cmd+T (macOS).
  • Confirm My table has headers is checked, then click OK.
  • Save the file with Ctrl+S (Windows) or Cmd+S (macOS).

Your Equipment sheet should now show alternating row colors and filter arrows on all 6 column headers.

Table range only includes some rows?

If Excel only captured a few rows in the table, you may have a blank row in the middle of your data. Excel stops detecting the table range at the first completely empty row.

Delete any empty rows between your data, then undo the table (Ctrl+Z) and try Ctrl+T again.

My Excel table isn't capturing all my equipment rows.

Your data foundation is complete. Next up, you'll import these three tables into Power BI and transform them into a connected data model ready for visualization.

Import and Transform Data in Power BI

Your Excel data is structured and ready to go. Now it's time to bring those three tables into Power BI where they can form relationships and power real visuals.

Before data enters the model, Power BI runs it through Power Query Editor. This is your quality gate for catching mismatched data types (like dates stored as text) that would silently break your calculations later.

In this step, get ready to:
  • Connect Power BI to your Excel workbook.
  • Validate and fix data types in Power Query Editor.
  • Load data into the model and establish table relationships.
Connect to your Excel file

Power BI's Get Data feature connects to dozens of source types. You'll point it at the Excel workbook you built in the previous step.

  • In Power BI Desktop, click Get Data on the Home ribbon.
  • Select Excel Workbook from the dropdown menu.
  • Browse to your PipelineTracker.xlsx file and click Open.
  • In the Navigator pane, check all three tables: Projects, Activities, and Equipment.
  • Click Transform Data (not Load) to open the Power Query Editor.

Why Transform Data instead of Load?

Clicking Transform Data opens Power Query Editor, where you can inspect and fix data types before they enter the model. If you click Load directly, Power BI guesses the types automatically. Those guesses are often wrong for date columns.

Don't see your tables in the Navigator?

Make sure you formatted each sheet as an Excel Table in the previous step (select all data, then Ctrl+T). Power BI only reliably detects properly formatted Excel Tables.

Also confirm you saved and closed the workbook in Excel before connecting from Power BI.

Help me troubleshoot my Navigator pane in Power BI

Clean and validate data types

Date columns that import as text will silently break time-based calculations later. This substep ensures every column has the correct type before data enters the model.

  • In Power Query Editor, select the Projects table from the Queries pane on the left.
  • Look at the small icon to the left of the StartDate column header. If it shows a calendar icon, the type is already Date. If it shows "ABC" or "123", click the icon and select Date from the dropdown.
  • Repeat for the EndDate column. Ensure it also shows the Date type.
  • Click the icon on the Budget column header. Verify it is set to Whole Number or Decimal Number. Change it if needed.

Now do the same validation on the Activities table.

  • Select the Activities table in the Queries pane.
  • Verify that PlannedStart, PlannedEnd, ActualStart, and ActualEnd are all set to Date type. Click the column header icon to change any that are not.
  • Verify that PercentComplete is set to Decimal Number type.
  • Verify that ActualCost is set to Decimal Number type.

Why is PercentComplete a Decimal Number?

Your PercentComplete values are stored as decimals between 0 and 1 (e.g., 0.5 means 50% complete). This makes DAX division simple in the next step. If the column were stored as whole numbers (0 to 100), every formula would need to divide by 100 first.

Seeing errors after changing a data type?

If Power BI asks whether to replace or add a new step, choose Replace current. This avoids duplicate conversion steps that can cause errors.

If a column shows "Error" values after changing to Date, your source data likely has inconsistent date formats. Go back to Excel and make sure all dates in that column use the same format (e.g., YYYY-MM-DD).

Help me fix data type errors in Power Query

Apply changes and load

With your data types validated, it's time to load everything into the Power BI data model and confirm the relationships between your tables.

  • In Power Query Editor, click Close & Apply on the Home ribbon.
  • Wait for Power BI to load all three tables. You'll see a progress indicator in the bottom-right corner.
  • Once loading completes, click the Model view icon in the left sidebar (it looks like three connected boxes).

You should see your three tables displayed as boxes. Power BI may have auto-detected the relationships using the ProjectID columns. Look for lines connecting the tables.

✔️ I see lines connecting my tables

Power BI auto-detected your relationships. Verify that the lines connect ProjectID in Activities to ProjectID in Projects, and ProjectID in Equipment to ProjectID in Projects. You're all set for the next step.

ⓧ I don't see any connecting lines

No worries. You can create relationships manually by dragging fields between tables.

  • In Model view, find the ProjectID field in the Activities table box.
  • Click and drag ProjectID from Activities and drop it onto ProjectID in the Projects table box. A line appears connecting them.
  • Repeat for Equipment: drag ProjectID from the Equipment table and drop it onto ProjectID in the Projects table.

You should now see two relationship lines, both pointing from the outer tables (Activities, Equipment) into the central Projects table.

What is a star schema?

Your Model view now shows a star schema. Projects sits at the center as the "dimension" table, with Activities and Equipment radiating out as related tables linked by ProjectID.

This structure lets Power BI slice any activity or equipment metric by project attributes (client, pipeline, status) without duplicating data. It's the recommended pattern for project tracking dashboards.

Your data is loaded, typed correctly, and linked through a clean star schema. Next up, you'll write DAX measures that turn this raw data into real KPIs like completion percentage, budget variance, and equipment readiness.

Build DAX Measures for Project KPIs

Your data is loaded and your tables are connected. Now you need to turn that raw data into meaningful numbers that a project manager can act on.

In this step, you will create DAX measures that calculate budget variance, completion percentage, overdue activities, and equipment readiness. These are the core KPIs that drive decision-making on pipeline projects.

In this step, get ready to:
  • Create a dedicated measures table to keep your DAX formulas organized.
  • Build core KPI measures for budget tracking and project completion.
  • Add equipment status measures and a RAG status column for visual reporting.
Create a measures table

DAX measures need to live inside a table. Instead of scattering measures across your Projects or Activities tables, a dedicated container keeps everything tidy and easy to find.

  • In Power BI Desktop, make sure you are in Report view (the chart icon in the left sidebar).
  • Click Enter Data on the Home ribbon.
  • In the Create Table dialog, leave the data area blank.
  • Change the table name at the top to _Measures (the underscore keeps it sorted at the top of your Data pane).
  • Click Load.

You should see _Measures listed in the Data pane on the right side of your screen.

Why a separate _Measures table?

Power BI lets you attach measures to any table, but grouping them in one place makes your model cleaner. The _Measures table appears at the top of the Data pane so you can always find your KPIs quickly.

Don't see the _Measures table?

Make sure you clicked Load (not Edit) in the Create Table dialog. If you accidentally opened Power Query Editor, close it without applying and try again.

help me find the _Measures table in the Data pane

Create core KPI measures

Now you will add measures that calculate the numbers your dashboard needs. Each measure uses DAX, a formula language built into Power BI that works like Excel formulas but operates across your entire data model.

  • In the Data pane, right-click the _Measures table and select New measure.
  • In the formula bar that appears at the top of the canvas, type the following DAX formula and press Enter to save it:
Total Budget = SUM(Projects[Budget])

What does this measure do?

SUM(Projects[Budget]) adds up every value in the Budget column of your Projects table. The result updates dynamically when you filter by project or status.

  • Right-click _Measures again and select New measure. Enter this formula:
Total Actual Cost = SUM(Activities[ActualCost])
  • Create a third measure by right-clicking _Measures and selecting New measure:
Budget Variance = [Total Budget] - [Total Actual Cost]

What does the square bracket syntax mean?

When you reference another measure inside a DAX formula, you wrap its name in square brackets like [Total Budget]. This tells Power BI to use the result of that measure, not a column.

  • Create a fourth measure:
Budget Variance % = DIVIDE([Budget Variance], [Total Budget], 0)

Why use DIVIDE instead of a slash?

The DIVIDE function safely handles division by zero. If [Total Budget] is zero, the formula returns 0 instead of an error. The third argument (0) is the alternate result.

  • Create a fifth measure for project completion tracking:
Completion % = DIVIDE(COUNTROWS(FILTER(Activities, Activities[PercentComplete] = 1)), COUNTROWS(Activities), 0)

What does this formula calculate?

FILTER(Activities, Activities[PercentComplete] = 1) returns only the rows where PercentComplete equals 1 (100% done). COUNTROWS counts those rows. DIVIDE then divides completed activities by total activities to give you a ratio.

  • Create a sixth measure to flag overdue work:
Overdue Activities = COUNTROWS(FILTER(Activities, Activities[PlannedEnd] < TODAY() && Activities[PercentComplete] < 1))

How does this identify overdue activities?

The FILTER function looks for rows where the planned end date has already passed AND the activity is not yet 100% complete. COUNTROWS counts how many activities match both conditions.

After entering all six measures, expand the _Measures table in the Data pane. You should see all six measures listed with calculator icons next to their names.

Seeing a formula error?

Check that column names match your data exactly. Projects[Budget] must match the column header in your Excel table. If you named the column differently (like TotalBudget), update the formula to match.

Also check that you are entering each formula in the formula bar at the top of the canvas, not inside a text box on the report.

help me fix a DAX formula error in Power BI

Create equipment status measure and RAG column

Your final measures track equipment certification status and assign a traffic-light colour to each activity based on its progress.

  • Right-click the _Measures table and select New measure. Enter this formula:
Certified Equipment % = DIVIDE(COUNTROWS(FILTER(Equipment, Equipment[Status] = "Certified")), COUNTROWS(Equipment), 0)

What does this measure do?

This counts how many equipment items have a Status of Certified and divides by the total equipment count. The result tells you what percentage of your tool inventory is cleared for use.

Next, you will add a calculated column to the Activities table. A calculated column is different from a measure. It adds a new field to every row in the table, evaluated row by row.

  • In the Data pane, right-click the Activities table (not _Measures) and select New column.
  • In the formula bar, enter the following DAX formula and press Enter:
RAG Status = SWITCH(TRUE(), Activities[PercentComplete] = 1, "Green", Activities[PercentComplete] >= 0.5 && Activities[PlannedEnd] >= TODAY(), "Amber", "Red")

How does the RAG Status logic work?

SWITCH(TRUE(), ...) evaluates conditions in order and returns the first match. The logic is:

  • If PercentComplete equals 1 (activity is done), return Green.
  • If PercentComplete is at least 0.5 AND the planned end date has not passed, return Amber.
  • Everything else falls through to Red (behind schedule or barely started).

RAG stands for Red, Amber, Green. It is a standard traffic-light system used across project management in oil and gas to flag activity health at a glance.

Expand the Activities table in the Data pane. You should see RAG Status listed as a new column alongside your existing fields.

RAG Status not appearing?

Make sure you right-clicked the Activities table and chose New column (not New measure). A measure does not add a row-level field. You need a calculated column for this.

help me add a calculated column to the Activities table in Power BI

Your KPI calculations are locked in. Next up, you will arrange these measures into a polished dashboard layout with cards, charts, and conditional formatting that brings your RAG status to life.

Design Your Dashboard Layout

Your data model is ready and your DAX measures are calculating key metrics behind the scenes. Now it's time to make those numbers visible.

In this step, you'll build the actual dashboard that a project coordinator would present to stakeholders. You'll add KPI cards, a progress table with color-coded status, charts for budget tracking, and slicers for interactive filtering.

In this step, get ready to:
  • Create a KPI summary row with Card visuals showing budget and progress metrics.
  • Add a project progress table with conditional RAG formatting.
  • Add charts and slicers to make the dashboard interactive.
Create the KPI summary row

Executive dashboards always lead with high-level numbers. Card visuals in Power BI display a single key metric prominently, making them perfect for a KPI row at the top of your report.

  • Switch to Report view using the left sidebar icon in Power BI Desktop (it looks like a chart).
  • Click an empty area of the canvas to make sure nothing is selected.
  • In the Visualizations pane on the right, select the Card visual from the visual gallery.
  • In the Data pane, expand the _Measures table.
  • Drag Total Budget into the Card's Values field well.
  • Drag the card to the top-left corner of the canvas and resize it to take up roughly a quarter of the width.

The card now displays your total budget figure. Repeat this process to create three more cards across the top.

  • Click an empty area of the canvas, then select the Card visual again from the Visualizations pane.
  • Drag Total Actual Cost from _Measures into the Values field well.
  • Position this card to the right of the first one.
  • Create a third Card and assign Budget Variance % to its Values.
  • Create a fourth Card and assign Overdue Activities to its Values.
  • Align all four cards in a single row across the top of the canvas.

Now format the cards so the numbers are easy to read at a glance.

  • Select the Total Budget card, then click the Format visual icon (paint brush) in the Visualizations pane.
  • Under the Visual tab, expand Callout and set the display units or decimal places as needed for currency.
  • Repeat this formatting for the Total Actual Cost card (currency).
  • Format the Budget Variance % card to display as a percentage.
  • Leave the Overdue Activities card as a whole number.

You should now see four card visuals in a row at the top of your canvas, each showing a different KPI value with proper formatting.

Cards showing blank or an error?

Make sure you dragged measures from the _Measures table, not raw columns from other tables. If a card is blank, your measure may reference a column name that doesn't match your data exactly.

Help me troubleshoot blank Card visuals

Add the project progress table

A progress table gives stakeholders line-by-line visibility into each activity. Adding conditional formatting with RAG colors (Red, Amber, Green) makes it instantly scannable.

  • Click an empty area of the canvas below your KPI cards.
  • In the Visualizations pane, select the Table visual.
  • In the Data pane, expand the Activities table.
  • Drag ActivityName into the table's Columns field well.
  • Drag Phase into Columns.
  • Drag PercentComplete into Columns.
  • Drag RAG Status into Columns.
  • Resize the table so it fills the lower portion of the canvas.

You should see rows of activities with their completion percentages and RAG Status text values. Now apply conditional formatting so the RAG Status column is color-coded.

  • Select the Table visual on the canvas.
  • In the Columns field well, click the down-arrow next to RAG Status.
  • Select Conditional formatting, then select Background color.
  • In the dialog that opens, set Format style to Rules.
  • Under What field should we base this on?, select RAG Status.
  • Create a rule where the value equals Red and set the background color to red.
  • Create a rule where the value equals Amber and set the background color to yellow/amber.
  • Create a rule where the value equals Green and set the background color to green.
  • Click OK to apply.

Your table's RAG Status column should now display colored backgrounds: red cells for overdue items, amber for in-progress, and green for completed.

Why RAG status matters in pipeline projects

RAG (Red, Amber, Green) is the universal traffic-light system used in project management reporting. It lets stakeholders identify at-risk activities at a glance without reading every row.

Conditional formatting not applying?

Make sure you selected Rules as the Format style (not Gradient or Field value). Also verify the values in your rules match the exact text your RAG Status column produces: Red, Amber, and Green (case-sensitive).

Help me fix my conditional formatting

Add charts and slicers

Charts visualize budget comparisons at a glance. Slicers are interactive filter controls that let anyone viewing the dashboard drill into specific projects, phases, or statuses without touching the underlying data.

  • Click an empty area of the canvas in the middle section (between your KPI cards and the table).
  • In the Visualizations pane, select the Clustered Bar Chart visual.
  • Drag Projects[ProjectName] to the Y-axis field well.
  • Drag Total Budget from _Measures into the X-axis field well.
  • Drag Total Actual Cost into the same X-axis field well, so both measures appear side by side.
  • Resize the chart to fill the left half of the middle area.

What does this chart show?

The clustered bar chart compares planned budget against actual spend for each project. Bars that extend far beyond the budget bar indicate cost overruns, a critical signal in pipeline operations.

Next, add a donut chart for equipment certification status.

  • Click an empty area of the canvas to the right of your bar chart.
  • In the Visualizations pane, select the Donut Chart visual.
  • Drag Equipment[Status] into the Legend field well.
  • Drag Equipment[EquipmentID] into the Values field well. Power BI defaults to Count of EquipmentID.
  • Resize the donut chart to fit in the right half of the middle area.

What does the donut chart show?

Each slice represents an equipment certification status: Certified, Expiring Soon, or Expired. This gives project coordinators immediate visibility into whether their tool inventory is deployment-ready.

Finally, add slicers so the dashboard becomes interactive.

  • Click an empty area of the canvas (a sidebar area or above the charts works well).
  • In the Visualizations pane, select the Slicer visual.
  • Drag Projects[Status] into the slicer's Field well.
  • Click an empty area and create a second Slicer with Activities[Phase].
  • Create a third Slicer with Projects[ProjectName].
  • Arrange all visuals neatly: KPI cards at the top, charts in the middle, the detail table at the bottom, and slicers along the side or top.

Test your slicers to confirm the dashboard is interactive.

  • Click a value in any slicer (for example, click Active in the Status slicer).
  • Verify that all other visuals on the page filter to show only active projects.
  • Click the value again (or use the eraser icon on the slicer) to clear the filter.
  • Save your file by pressing Ctrl+S (Windows) or Cmd+S (macOS).

Slicers not filtering other visuals?

Slicers filter all visuals on the same page by default. If a visual is not responding, check that the tables are related in the Model view. The slicer's field must connect to the visual's data through a relationship on ProjectID.

Help me check my table relationships

Your Pipeline Project Tracker Dashboard is complete. You now have a professional-grade report that transforms raw field data into the kind of executive-level visibility project coordinators and managers rely on every day.

Secret mission

Add a Daily Progress Report Page

Your main dashboard gives a high-level view of project health. But on a real pipeline job, supervisors need daily output metrics. Build a second report page that tracks crew productivity day by day and surfaces your average tasks completed per shift.

Clean Up Your Resources

Clean Up Your Resources

This project runs entirely locally on your machine using Power BI Desktop and Excel. There are no cloud resources or ongoing costs to worry about.

Decide whether to keep your files and software for future use, or delete them entirely.

Resources you used:

  • PipelineTracker.xlsx (your Excel data workbook with Projects, Activities, Equipment, and DailyProgress tables).
  • PipelineTracker.pbix (your Power BI Desktop dashboard file).
  • Power BI Desktop (installed application).

Keep everything running

No action needed. Choose this if you plan to keep building on your dashboard or want to revisit it later.

  • Your PipelineTracker.pbix and PipelineTracker.xlsx files stay where you saved them.
  • Power BI Desktop remains installed and ready to open your dashboard at any time.
  • Power BI Desktop is free for local use, so keeping it installed costs nothing.

Pause - I'll come back to this later

Close Power BI Desktop to free up system memory. Your files are already saved and ready to reopen whenever you return.

  • Close Power BI Desktop if it is still open.
  • Your .pbix and .xlsx files remain on disk. To resume, double-click PipelineTracker.pbix to reopen your dashboard.

Delete - I don't want to use this again

Remove all project files and optionally uninstall Power BI Desktop to start fresh.

  • Delete PipelineTracker.xlsx and PipelineTracker.pbix from the folder where you saved them (right-click each file and select Delete).
  • Empty your Recycle Bin to permanently remove the files.
  • To uninstall Power BI Desktop, open Settings on your computer.
  • Select Apps, then Apps & features.
  • Find Power BI Desktop in the list, select it, and click Uninstall.
  • Follow the prompts to complete the removal.

Nice Work!

Nice Work!

You just built a professional Pipeline Project Tracker Dashboard that transforms raw field data into executive-level project visibility. This is exactly the kind of reporting that project coordinators and project engineers deliver in the oil and gas industry.

You've learned how to:

  • Structure pipeline project data using a star schema with linked Projects, Activities, and Equipment tables in Excel and Power BI.
  • Create DAX measures that calculate budget variance, completion percentage, overdue activities, and equipment certification status.
  • Design an interactive dashboard with KPI cards, RAG conditional formatting, clustered bar charts, donut charts, and slicers for filtering.
  • Secret Mission: Added a Daily Progress Report page with a trend line chart and daily crew output metrics.

Ready to quiz yourself?