Building the SafariConnect Dashboard

Wednesday's job was simple to state and hard to do: turn a PostgreSQL schema full of correct answers into something a CEO could read in ten seconds. By Monday we had clean bookings. By Tuesday we had answers to six business questions, buried in SQL output. By Thursday morning, someone with no interest in GROUP BY clauses needed to walk out of a meeting knowing which routes made money and which drivers to promote.

That gap between a correct query and a convincing dashboard is where this part of the project actually lived.

Getting the Data Ready for Power BI

Power BI doesn't want to see your cleaning process. It wants a table it can trust, so the first job was making sure v_clean_trips and clean_bookings deserved that trust. The reason we use both sources for the presentation is that v_clean_trips only contains the completed trips and clean_bookings contains both the completed and cancelled trips, which is a major presentation point. We'd already fixed the 23 data problems in the raw CSV such as the shouting names, the phone numbers with dashes and stray +254 prefixes, the seat classes spelled four different ways and the fares stored as text. Wednesday's task was different. It was about shape, not spelling.

2 quick reminders before getting into the dashboard building process:

1. To go through the step-by-step analysis that led to this Power BI report, go through this article.
2. For a refresher on how to connect Power BI to a local database, check out this article

A few things mattered more than I expected going in:

• Grain. Power BI aggregates whatever you drag onto a visual, and it will do it happily even when the grain is wrong. Our view sits at one row per booking, so SUM(seats_booked) gives real passenger counts and COUNT(booking_id) gives real booking counts. Mixing those up produces numbers that look plausible and are wrong, which is worse than numbers that look obviously broken.
• Date and time columns split out early. We pulled day_name and hour-of-day fields out of departure_date and departure_time in SQL rather than in Power Query, because a CTE with EXTRACT() is easier to check with a SELECT than a DAX measure buried in a report. Anything I can verify with a query, I verify with a query.
• Indexes, because refresh time is part of the user experience. Seven indexes went onto the clean table before we connected Power BI, on the columns we knew we'd filter and join on constantly: route_code, driver_name, booking_status, departure_date. A dashboard that takes fifteen seconds to slice by route stops feeling like a dashboard and starts feeling like a problem.
• One view, not five imports. Everything Power BI touches comes from v_clean_trips, clean_bookings or a small set of purpose-built views. That's a deliberate choice. When a number on a slide looks wrong, there's exactly one place to go check it, not five copies of the logic drifting apart across different report pages.

The Dashboard, Page by Page

We built five pages, ending in one summary view stitched together from the other four. Here's what each one carries.

Page 1 sets the scale of the business in seven cards.

Total revenue of KSh 259.96K across 288 bookings, an average fare of KSh 903.10, an average driver rating of 4.27 against an average trip rating of only 3.53, and 35 cancelled bookings. Nothing fancy here on purpose. A board member glancing at a laptop from across a table should get the shape of the business before anyone says a word.

Page 2 is where the money and the people live.

A route table anchors the page on the left, followed by a revenue-by-route bar chart, a monthly revenue line, a passenger-city chart, a gender donut, and a driver rating bar chart with a trend line. RT001, Nairobi to Mombasa, sits clearly on top of the revenue bars, matching what the SQL had already told us on Tuesday. Good sign. If the dashboard had disagreed with the query, one of them would have been lying.

Page 3 covers what's costing us and when people actually travel.

A seat-class donut, a cancellation-rate card, a lost-revenue figure, a route-by-route lost revenue bar chart, and a month-by-day-of-week matrix of bookings. The cancellation rate reads as 0.12, which is 12%, and lost revenue lands at KSh 32.15K. That's real money walking out the door on bookings that never happened.

Page 4 is one focused chart.

Busiest hours by departure time, sorted from most booked to least. 6am leads. This page could have lived inside page 3, but a scheduling question this operational deserved its own screen rather than fighting for space with six other visuals.

The final page compresses all of it onto one screen for anyone who wants the whole story without clicking through tabs.

The DAX Behind Each Metric

Power BI doesn't inherit anything from the SQL side beyond the columns themselves, so every card and chart above needed its own measure. Below is the DAX that reproduces each one, written against clean_bookings as the table name, swap in whatever your model actually calls it.

KPI cards (Page 1 and the dashboard header)

Total Revenue = SUM(v_clean_trips[total_fare])

Total Bookings = DISTINCTCOUNT(v_clean_trips[booking_id])

Avg Fare = DIVIDE([Total Revenue], [Total Bookings])

Avg Driver Rating = AVERAGE(v_clean_trips[driver_rating])

Avg Trip Rating = AVERAGE(v_clean_trips[trip_rating])

Cancelled Bookings =
CALCULATE(
    DISTINCTCOUNT(clean_bookings[booking_id]),
    clean_bookings[booking_status] = "Cancelled"
)

Lost Revenue =
CALCULATE(
    SUM(clean_bookings[total_fare]),
    clean_bookings[booking_status] IN { "Cancelled", "No Show" }
)

Cancellation Rate % =
DIVIDE(
    CALCULATE(
        DISTINCTCOUNT(clean_bookings[booking_id]),
        clean_bookings[booking_status] IN { "Cancelled", "No Show" }
    ),
    [Total Bookings]
)

Revenue by Route and Revenue by Month (Page 2)

Revenue by Route =
CALCULATE(
    SUM(v_clean_trips[total_fare]),
    ALLEXCEPT(v_clean_trips, v_clean_trips[route_code])
)

Revenue by Month = SUM(v_clean_trips[total_fare])
-- placed on a matrix/line chart with departure_date grouped to Month,
-- or against a proper Date table:
Monthly Revenue = CALCULATE(SUM(v_clean_trips[total_fare]), DATESMTD('Date'[Date]))

Revenue by Route is really just Total Revenue dropped onto a bar chart with route_code on the axis; the ALLEXCEPT version is only needed if you want the route total to appear as a static comparison column alongside row-level detail.

Passenger Population and Gender split (Page 2)

Total Seats Booked = SUM(v_clean_trips[seats_booked])

Female Passengers =
CALCULATE([Total Seats Booked], v_clean_trips[passenger_gender] = "Female")

Male Passengers =
CALCULATE([Total Seats Booked], v_clean_trips[passenger_gender] = "Male")

Both donut and area chart use [Total Seats Booked], split by passenger_city for the area chart and by passenger_gender for the donut.

Driver Rating per Revenue (Page 2)

Driver Avg Rating = AVERAGE(v_clean_trips[driver_rating])

Driver Total Revenue = SUM(v_clean_trips[total_fare])

Both measures sit on the same combo chart with driver_name on the axis, one as columns, one as the line. The duplicate "Sum of driver_rating" legend entry flagged in the critique below is what happens when the second measure gets built as a copy of the first instead of a trip_rating average.

Seat Class Booked and Cancellation Rate (Page 3)

Bookings by Seat Class = COUNTROWS(clean_bookings)
-- on the donut, sliced by seat_class

Lost Revenue by Route =
CALCULATE(
    SUM(clean_bookings[total_fare]),
    clean_bookings[booking_status] IN { "Cancelled", "No Show" },
    ALLEXCEPT(clean_bookings, clean_bookings[route_code])
)

Busiest Days Booked by Seats and Busiest Days per Bookings (Page 3)

Seats Booked by Day =
SUM(v_clean_trips[seats_booked])
-- matrix: day_name on rows, Month(departure_date) on columns

Bookings by Day of Week =
COUNTROWS(v_clean_trips)
-- bar chart: WEEKDAY(v_clean_trips[departure_date]) on the axis

day_name itself is worth calculating once as a calculated column rather than re-deriving it in every visual:

day_name = FORMAT(v_clean_trips[departure_date], "dddd")
day_of_week = WEEKDAY(v_clean_trips[departure_date], 2)

Busiest Hours per Departure Time (Page 4)

Bookings by Departure Hour = COUNTROWS(v_clean_trips)
-- horizontal bar, departure_time on the axis, sorted by this measure descending

If departure_time came in as text (as it did through most of the SQL cleaning stage), Power Query needs to convert it to a proper time value before this sort works correctly, otherwise "06:00" and "16:00" sort as text rather than as clock time.

What the Numbers Actually Said

Strip away the chart types and a few things stood out clearly enough that we could say them out loud without hedging.

Nairobi-to-Mombasa (RT001) is the business's best route by revenue, and it's also the route bleeding the most money to cancellations, at roughly KSh 10K lost against a lost-revenue total of KSh 32.15K. That's not a contradiction. It's the same thing: your busiest, most valuable route is also your biggest exposure when a booking falls through.

Driver ratings sit meaningfully higher than trip ratings across the board, 4.27 against 3.53. Passengers aren't rating the drivers badly, but something about the overall trip experience, the vehicle, the timing, the route itself, isn't landing as well as the person behind the wheel. That gap is worth a follow-up question the dashboard alone can't answer.

Economy dominates seat class choice by a wide margin, which says more about price sensitivity in this market than anything else in the data. Monday is the single busiest day of the week, ahead of Wednesday and Tuesday, and Sunday is dramatically quiet. 6am is the most requested departure slot, which tells operations exactly where to put the extra vehicle if they only have one to spare.

Telling the Story to the Board

The brief was blunt about Thursday: ten minutes, live dashboard, every group member speaks, and the CEO chooses who answers each question. That last part changes how you prepare more than anything else. You can't assign yourself the easy slide and hope nobody asks about the hard one.

We walked the room through the dashboard in the same order it's built here: scale first, then routes and people, then costs and timing. Each number on screen had to trace back to a specific query from Tuesday, because "the dashboard says so" isn't an answer a CEO in that kind of room will accept. When the cancellation-rate card came up, the follow-up was immediate: which route, how much money, and what would fixing it require. Having the lost-revenue-by-route chart sitting right there, rather than a number we'd have to go calculate live, was the difference between a confident answer and an awkward pause.

What This Taught Me

• A live filter is a live risk. Click the wrong slicer in front of an audience and your carefully rehearsed narrative shows a different number than the one you said thirty seconds ago. We tested every click path the night before, not just the happy one.
• A chart is not an insight. "Revenue by route" is a chart. "RT001 makes the most money and loses the most to cancellations" is an insight, and it only exists because someone said it out loud while pointing at two different visuals on two different pages. The dashboard did the arithmetic. The presentation did the thinking.
• Numbers need to match everywhere or they undermine themselves. The moment two pages showed a slightly different split for the same field, the room noticed before we did. A dashboard is a set of promises that every number agrees with every other number, and it only takes one mismatch to make people doubt all of them.

What I'd Build Differently Next Time

A few things would change if I rebuilt this dashboard today:

• The seat-class donut on page 3 carries a (Blank) slice worth 10-13% of bookings, a leftover null that should have been resolved to a real value or an explicit "Unknown" label back in the SQL cleaning stage, not left to surface as an unlabelled wedge on a board slide. The same donut also disagrees slightly on the combined summary page versus its own dedicated page, a small inconsistency that's exactly the kind of thing point three above warned about.
• The driver rating chart on page 2 shows two identical legend entries, both labelled "Sum of driver_rating." One of those measures was clearly meant to be something else, likely a trip-rating comparison, and it slipped through review. Labelling errors like that are easy to miss when you're staring at your own report and easy to spot when a CEO is staring at it fresh.
• Several labels get cut off: the route table's route_from column, the passenger-city axis, a driver's name mid-word on the ratings chart. On a laptop screen that's a minor irritation. Projected on a boardroom wall, it reads as sloppiness that has nothing to do with the analysis underneath it.
• Everything on this dashboard is the same shade of blue. That's a deliberate, calm choice, and it also means nothing visually flags the routes losing money versus the routes making it. A single accent color on the lost-revenue chart, red or amber against the house blue, would let a CEO's eye find the problem before anyone finished the sentence.
• Given that the entire business is about routes between named cities, there's no map anywhere in the report. A route table with codes and city names asks the reader to do geography homework. A map does that work for them, and it's the one visual type this dataset was practically begging for.

None of these are failures of the analysis. The numbers underneath the dashboard held up under direct CEO questioning, which was the actual test. But a dashboard has two audiences, the analyst who trusts the query behind it, and the executive who only ever sees the screen, and building for the second audience is a distinct skill from the SQL that got us there.