Building the Tembo Hotel Dashboard in Power BI

The Hotel Director's message ended with its shortest instruction: "Make it look professional - use Power BI for the visuals."

Professional is hard to test, so I use three tests. The numbers must tie to the database. The labels must say what they mean. A busy manager must find an answer in under a minute. This article builds the Tembo Hotel dashboard, then grades it against all three.

I'll also set you a puzzle. Page three of the dashboard contains a mistake that sits in plain sight. Look at the Staff Performance table when you reach it, and see whether you spot it before I explain.

The brief in one breath

Tembo Hotel & Suites is a mid-range business hotel in Nairobi. Its Director wanted four answers: which rooms make the most money, which months are busiest, how the staff are performing, and whether guests are happy. The raw material was a booking export with 286 rows and a long list of defects. I cleaned it in PostgreSQL first and then built the dashboard on the clean result.

3 quick reminders before building the dashboard:

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.
3. To access the files used to build this project, check out my Github repository.

1. How I prepared the data for BI

A dashboard does not clean data. It draws whatever it receives, so a misspelt city becomes a bar and a duplicate booking becomes double revenue. The cleaning therefore came first, in SQL, and Power BI only ever saw the finished result: 285 bookings after one exact duplicate was removed.

Import the view, not the table

Power BI imports one object, the view v_clean_bookings in the tembo_hotel schema of the PostgreSQL database. It does not touch the staging table or the raw file. The model is a single flat table of 285 rows, with 25 measures on top.

The view carries four columns built for reporting:

Column	What it holds
realised_revenue	The booking amount for Checked Out bookings, and zero for the rest
lost_revenue	The booking amount for Cancelled and No Show bookings, and zero for the rest
stay_month	The first day of the check-in month
month_label	The same month as text, such as 2023-06

Moving the two money rules into SQL was deliberate. Power BI then sums a column, and anything else that reads the view gets the same answer.

Three signs the dashboard reads clean data

• The first card says 285 bookings, so the duplicate is gone.
• The guest-city chart has a bar called Unknown. That is the label my cleaning gave to blank cities. It has no bar called Thikax, a typo the cleaning corrected to Thika.
• Every chart that splits by category shows exactly the allowed values: four payment methods, four room types, three booking statuses. There is no mpesa, Std or DLX.

Four rules the numbers rest on

Cleaning fixes spelling. It cannot decide what "revenue" means. Before building a chart, I fixed four rules:

Term	Rule	Where it lives
Realised revenue	Checked Out bookings only	SQL column realised_revenue
Lost value	Cancelled and No Show bookings	SQL column lost_revenue
Completed stay	A booking with status Checked Out	DAX measure
Average rating	Completed stays only; blank ratings stay blank	DAX measure

Section 3 shows the DAX for both rules, and for every other measure on the three pages. AVERAGE skips blank cells, so the NULL ratings left by the cleaning step never count as zeros. Nobody rates a night they did not spend, so ratings on cancelled bookings stay out of the average too.

I sorted the 25 measures into numbered display folders (Booking Volume, Revenue, Room Demand, Guest Satisfaction, Staff Performance, Booking Losses, Monthly Trend) and gave each one a one-line description. When someone asks what "Lost Revenue" means, the model answers.

Does the dashboard agree with the database?

I do not trust a dashboard until its totals match a query I wrote by hand. This query returns the headline numbers straight from the view:

SELECT COUNT(*) AS total_bookings,
       COUNT(*) FILTER (WHERE booking_status = 'Checked Out') AS completed_stays,
       SUM(total_amount) FILTER (WHERE booking_status = 'Checked Out') AS realised_revenue,
       COUNT(*) FILTER (WHERE booking_status IN ('Cancelled', 'No Show')) AS lost_bookings,
       SUM(total_amount) FILTER (WHERE booking_status IN ('Cancelled', 'No Show')) AS lost_value,
       ROUND(AVG(guest_rating) FILTER (WHERE booking_status = 'Checked Out'), 2) AS avg_rating
FROM tembo_hotel.v_clean_bookings;

Result grid: 1 row fetched

total_bookings	completed_stays	realised_revenue	lost_bookings	lost_value	avg_rating
285	253	7752400.00	32	1175300.00	3.01

Measure	Dashboard	SQL	Match
Total bookings	285	285	Yes
Completed stays	253	253	Yes
Realised revenue	7,752K on the card, 7,752,400 in the staff table total	7,752,400	Yes
Lost bookings	32	32	Yes
Lost value	1,175K on the card, 1,175,300 in the payment table	1,175,300	Yes
Average rating	3.01	3.01	Yes

Every figure ties. That is test one passed.

2. The dashboard I built

I grouped the Director's four questions into three pages. Each page has one job.

Page	Job	Visuals
Executive Overview	Money and trend at a glance	Five KPI cards, monthly realised revenue (line), revenue by room type (bars), revenue by payment method (donut), revenue share by department (bars), booked value by status (donut)
Rooms & Guests	Who stays, for how long, and how they feel	Completed stays by room type, average stay length by room type, completed stays by guest city, guest rating distribution, average rating by room type
Staff & Risk	People and lost money	Staff performance table, realised revenue by department, cancellation and no-show rate by room type, lost bookings by payment method

The look is deliberate and plain: a cream background, one teal for every bar, a serif title in warm brown, and four warm colours kept for the donuts, where slices must be told apart. The page tabs use the nouns the Director already uses.

The second page answers "who stays and are they happy". The rating distribution sits beside the average on purpose, since the average alone would mislead.

The third page holds the staff table, and the puzzle. Look at the Rank column before you read on.

3. The DAX behind each page

Every figure on the dashboard comes from a measure. The model has one table, named tembo_hotel v_clean_bookings, and 25 measures grouped in display folders. The code below is copied from the model and reformatted for reading, with the logic unchanged. For each page I list the visuals and the measure that feeds each one, then show the code for the measures that page introduces. A measure used on an earlier page is not repeated.

Two display details apply everywhere. The measures keep plain number formats (#,##0 for counts and money, 0.00 for ratings and average nights, 0.0% for shares and rates). The "K" on the cards and the payment donut comes from the visual's label display units, set to Thousands, so 7,752,400 shows as 7,752K.

Page 1: Executive Overview

Visual	Title on the page	Axis	Measure
Card	(none, a caption sits below)	none	[Total Bookings]
Card	Realised Revenue (KES)	none	[Realised Revenue]
Card	Average Guest Rating / 5	none	[Average Guest Rating]
Card	At-Risk Booking Value (KES)	none	[Lost Revenue]
Card	Completed Stays	none	[Completed Stays]
Line	Monthly Realised Revenue (KES)	month_label	[Realised Revenue]
Bars	Revenue by Room Type (KES)	room_type	[Realised Revenue]
Donut	Revenue by Payment Method (KES)	payment_method	[Realised Revenue]
Bars	Revenue Share by Department	staff_department	[Department Revenue Share]
Donut	Booked Value by Status	booking_status	[Booking Status Value Share]

The first block holds the volume and money measures. Two of them, Realised Revenue and Lost Revenue, simply sum the reporting columns that the SQL view built, so the rule sits in the database and DAX stays short.

Total Bookings =
COUNTROWS('tembo_hotel v_clean_bookings')

Completed Stays =
CALCULATE([Total Bookings], 'tembo_hotel v_clean_bookings'[booking_status] = "Checked Out")

Realised Revenue =
SUM('tembo_hotel v_clean_bookings'[realised_revenue])

Lost Revenue =
SUM('tembo_hotel v_clean_bookings'[lost_revenue])

Booked Value =
SUM('tembo_hotel v_clean_bookings'[total_amount])

Average Guest Rating =
CALCULATE(AVERAGE('tembo_hotel v_clean_bookings'[guest_rating]), 'tembo_hotel v_clean_bookings'[booking_status] = "Checked Out")

The two share charts need a different shape. The numerator is the bar's own value. The denominator is the same value with the filter on the bar's own category removed, which gives the total across all categories. DIVIDE returns a blank on a zero denominator and avoids an error.

Department Revenue Share =
DIVIDE(
    [Realised Revenue],
    CALCULATE([Realised Revenue], REMOVEFILTERS('tembo_hotel v_clean_bookings'[staff_department]))
)

Booking Status Value Share =
DIVIDE(
    [Booked Value],
    CALCULATE([Booked Value], REMOVEFILTERS('tembo_hotel v_clean_bookings'[booking_status]))
)

Page 2: Rooms & Guests

Visual	Title on the page	Axis	Measure
Columns	Completed Stays by Room Type	room_type	[Completed Stays]
Bars	Average Stay Length by Room Type	room_type	[Average Stay Nights]
Bars	Completed Stays by Guest City	guest_city	[Completed Stays]
Columns	Guest Rating Distribution	guest_rating	[Rating Distribution Share]
Bars	Average Rating by Room Type	room_type	[Average Guest Rating]

Page two reuses Completed Stays and Average Guest Rating from page one and adds three measures of its own. Average Stay Nights is built from a reusable Room Nights measure, so the two can be checked separately. Rated Stays counts the completed stays that carry a rating, and Rating Distribution Share divides each rating's count by the total across all ratings.

Room Nights =
CALCULATE(SUM('tembo_hotel v_clean_bookings'[nights_stayed]), 'tembo_hotel v_clean_bookings'[booking_status] = "Checked Out")

Average Stay Nights =
DIVIDE([Room Nights], [Completed Stays])

Rated Stays =
CALCULATE(COUNT('tembo_hotel v_clean_bookings'[guest_rating]), 'tembo_hotel v_clean_bookings'[booking_status] = "Checked Out")

Rating Distribution Share =
DIVIDE(
    [Rated Stays],
    CALCULATE([Rated Stays], REMOVEFILTERS('tembo_hotel v_clean_bookings'[guest_rating]))
)

COUNT skips blank cells, so Rated Stays counts only the 238 completed stays that have a rating. The unrated ones never produce a bar of their own. The rating chart's format string, 0.0%;-0.0%;0.0%, makes an empty share show as 0.0%.

Page 3: Staff & Risk

Visual	Title on the page	Fields	Measures
Table	Staff Performance	staff_name, staff_department	[Staff Booking Rank], [Bookings Handled], [Completed Stays], [Lost Bookings], [Realised Revenue]
Bars	Realised Revenue by Department (KES)	staff_department	[Realised Revenue]
Bars	Cancellation and No-Show Rate by Room Type	room_type	[Lost Booking Rate]
Table	Lost Bookings by Payment Method	payment_method	[Lost Bookings], [Lost Booking Rate] as Loss Rate, [Lost Revenue] as At-Risk Value, [Staff Handling Count]

Page three adds the staff and loss measures. COALESCE turns an empty result into a zero, which is why Card, Cash and M-Pesa show 0 in the payment table and not a blank. DIVIDE(..., 0) does the same for the rate. DISTINCTCOUNT counts each person once, so the total row shows 8 even though the four payment methods add up to more.

Bookings Handled =
[Total Bookings]

Lost Bookings =
COALESCE(
    CALCULATE([Total Bookings], 'tembo_hotel v_clean_bookings'[booking_status] IN {"Cancelled", "No Show"}),
    0
)

Lost Booking Rate =
DIVIDE([Lost Bookings], [Total Bookings], 0)

Staff Handling Count =
DISTINCTCOUNT('tembo_hotel v_clean_bookings'[staff_name])

Staff Booking Rank =
RANKX(ALL('tembo_hotel v_clean_bookings'[staff_name]), [Bookings Handled], , DESC, DENSE)

The last measure, Staff Booking Rank, feeds the Rank column in the staff table. It is the one to watch. Section 7 shows what it does wrong and how to fix it.

4. Key insights

Each number below comes from the cleaned data and appears on the dashboard.

1. The hotel realised 7,752,400 KES from 253 completed stays. That is 253 of 285 bookings, and 86.8% of everything booked by value.
2. Suites earn the most, Standard rooms fill the most. Suites brought in 2,659,600 KES (34.3%) from 54 stays. Standard rooms had 97 stays and 21.0% of revenue. One Penthouse stay is worth 64,611 KES on average, nearly four times a Standard stay at 16,789 KES.
3. Revenue stepped up in January 2024, then wobbled. Monthly revenue grew from 78,000 KES in June 2023 to 630,500 KES in January 2024. It peaks in April 2024 at 704,500 KES, drops to 437,700 KES in May (38% below April), and falls to its 2024 low of 402,500 KES in August before recovering. The 2023 months hold only 34 stays between them, against 18 in a typical 2024 month, so the early "growth" may reflect better record-keeping as much as better trade.
4. The 3.01 average rating hides a split. Of 238 ratings, 39.5% are a 1 or a 2, 43.7% are a 4 or a 5, and 16.8% are a 3. Average rating by room type runs from 2.94 (Deluxe) to 3.17 (Penthouse), too narrow a gap to act on.
5. Nairobi supplies 111 of the 253 stays, 43.9%. Kisumu (29) and Eldoret (28) follow far behind.
6. Volume and value rank different people first. Kelvin Omondi handled the most bookings (38). Brenda Achieng brought in the most revenue (1,283,800 KES). By department, Front Desk earns 30.4% of revenue, Restaurant 26.3%, Housekeeping 20.3%, Security 13.8% and Management 9.2%.
7. All the lost money sits in one payment method. Thirty-two bookings worth 1,175,300 KES were cancelled or never showed, which is 13.2% of booked value and 11.2% of bookings. Every one was paid by bank transfer, so 46.4% of bank-transfer bookings (32 of 69) failed. Cash, card and M-Pesa lost none. By room type, the Penthouse loses 24.0%, Standard 14.9%, Deluxe 7.8% and Suite 3.6%.

The seventh finding needs care. This query rebuilds the payment table from page three, with its last column:

SELECT payment_method,
       COUNT(*) FILTER (WHERE booking_status IN ('Cancelled', 'No Show')) AS lost_bookings,
       ROUND(100.0 * COUNT(*) FILTER (WHERE booking_status IN ('Cancelled', 'No Show'))
             / COUNT(*), 1) AS loss_rate_pct,
       COALESCE(SUM(total_amount) FILTER (WHERE booking_status IN ('Cancelled', 'No Show')), 0) AS at_risk_value,
       COUNT(DISTINCT staff_name) AS staff_handling
FROM tembo_hotel.v_clean_bookings
GROUP BY payment_method
ORDER BY payment_method;

Result grid: 4 rows fetched

payment_method	lost_bookings	loss_rate_pct	at_risk_value	staff_handling
Bank Transfer	32	46.4	1175300.00	2
Card	0	0.0	0	2
Cash	0	0.0	0	3
M-Pesa	0	0.0	0	3

The last column shows how many staff handle each method: two for bank transfer, two for card, three for cash, three for M-Pesa. Staff and payment method travel together in this file, so the data cannot say whether the cause is the payment method or the way two colleagues record cancellations. The dashboard shows the pattern. It does not claim a cause.

5. How I communicated the findings

I followed the Director's own words. His four questions became three pages, and the page names are plain nouns he already uses: Executive Overview, Rooms & Guests, Staff & Risk.

I put the answer before the evidence. The five cards across the top give the scale of the business in five seconds: 285 bookings, 7,752K realised, 3.01 out of 5, 1,175K lost, 253 completed stays. A reader who stops there still leaves with the shape of the hotel.

I let one colour do the work. Every bar is the same teal, so no bar competes with another for attention. The eye goes to length, which is the thing a bar chart exists to show.

I showed the spread beside the average. The rating page puts the full 1-to-5 distribution next to the average. Without it, 3.01 reads as "fine".

I presented the risk finding as a question. The "Staff Handling" column sits inside the lost-bookings table so that the reader sees the confound at the same moment as the pattern. A verdict would have been easier to say and harder to defend.

I checked the numbers against SQL first. Every headline ties to a query, so when someone asks "where does this come from?", the answer is a script and not a shrug.

6. What I learned from presenting the project

Define the word before showing the number. "Revenue" can mean booked, collected or profit. A one-line definition on the page, "realised revenue means checked-out stays only", costs ten seconds and heads off an argument.

Show the exact figure. The cards say 7,752K and 1,175K, which is precise enough to defend. The payment donut's centre says "8M", which rounds away 248,000 KES and contradicts the card beside it. One chart undoes what the other earned.

An average opens a conversation. It does not close one. The 3.01 rating is true and nearly useless until you see the split behind it.

Say what the data cannot tell you. The bank-transfer pattern is the most useful finding in the project, and the most dangerous to oversell. Naming its limit made the finding stronger.

Read your own dashboard like a stranger. Some defects are invisible to the person who built the page, because the builder knows what each label was meant to say. The Rank column, below, is one of them.

Small samples call for plain language. Nineteen months and 285 bookings can show a pattern. They cannot prove one. I try to write "suggests" where I would like to write "shows".

7. What I would do differently

First, what I would keep: the three-page structure, the single bar colour, the rating distribution beside the average, the thousands format on the cards, the honest "Staff Handling" column, and the model's tidy habits: display folders, a description on every measure, and the money rules kept in the database. The rest of this section is the repair list, sorted by how much each item matters.

Fix first: things that are wrong or misleading

The Rank column is wrong. That was the puzzle. On the Staff Performance table, six of eight people are ranked 1. Kelvin Omondi handled 38 bookings, two more than anyone, and four people tied on 36. Ranking the same data two ways in SQL shows the cause:

SELECT staff_name,
       staff_department,
       COUNT(*) AS bookings,
       DENSE_RANK() OVER (ORDER BY COUNT(*) DESC) AS hotel_rank,
       DENSE_RANK() OVER (PARTITION BY staff_department ORDER BY COUNT(*) DESC) AS rank_in_department
FROM tembo_hotel.v_clean_bookings
GROUP BY staff_name, staff_department
ORDER BY hotel_rank, staff_name;

Result grid: 8 rows fetched

staff_name	staff_department	bookings	hotel_rank	rank_in_department
Kelvin Omondi	Front Desk	38	1	1
Brenda Achieng	Front Desk	36	2	2
Fatuma Hassan	Housekeeping	36	2	1
Moses Kipchoge	Restaurant	36	2	1
Tony Karanja	Housekeeping	36	2	1
Amina Juma	Restaurant	35	3	2
Joy Otieno	Management	34	4	1
Peter Ngugi	Security	34	4	1

The dashboard's column matches rank_in_department: Amina 2, Brenda 2, Fatuma 1, Joy 1, Kelvin 1, Moses 1, Peter 1, Tony 1. The Staff Booking Rank measure from section 3 explains why. ALL on staff_name lifts the filter on the name, but the department column in the same table row still filters the calculation. Each person is ranked only against colleagues in their own department, and the total row, with no department filter, ranks 285 against eight smaller numbers and returns 1. The fix is to remove the department filter as well, and to show a rank only on a row that holds one person:

Staff Booking Rank =
IF(
    HASONEVALUE('tembo_hotel v_clean_bookings'[staff_name]),
    RANKX(
        ALL(
            'tembo_hotel v_clean_bookings'[staff_name],
            'tembo_hotel v_clean_bookings'[staff_department]
        ),
        [Bookings Handled], , DESC, DENSE
    )
)

Check the result against the hotel_rank column above, with dense ties: 1, 2, 2, 2, 2, 3, 4, 4. Then sort the table by bookings, highest first. The total row will show a blank rank, which is correct.

One idea has two names, and two rates have none. The card is titled "At-Risk Booking Value" and captioned "Lost Revenue", and the payment table says "At-Risk Value". These bookings are already lost, so "at risk" is wrong, and one name should win. Elsewhere, the status donut implies 13.2% lost (by value) while the staff page shows 11.2% (by count). Both are right. Each needs a label that says "of booked value" or "of bookings".

Two donuts carry filler at the centre. The payment donut shows "8M", which contradicts the 7,752K card. The status donut shows "100.0%", which tells the reader nothing. Use the space for the figure that matters, such as "13.2% lost".

The status donut repeats itself. Its labels read "10.2% (10.2%)", "3.0% (3.0%)" and "86.8% (86.8%)", because the measure Booking Status Value Share is already a share, and the donut adds its own percentage label on top. Show the category name and the value once.

Fix next: things that make the page hard to read

• Sort bars by value. Room types, departments, cities and payment methods all appear in alphabetical order, so the eye must hunt for the biggest bar. The brief also asked for the top 10 cities, and the city chart shows ten in alphabetical order, Unknown among them.
• Add data labels. Without them, the reader guesses at bar lengths. This matters most on the lost-rate chart, where a reader cannot tell 3.6% from 7.8% by eye.
• Rescale the rating chart. Bars that start at zero make 2.94 and 3.17 look identical. Show the share of 1-and-2 ratings against 4-and-5 ratings for each room type, which carries the real story.
• Put a title on pages two and three. Only the first page names itself, and each page should state its takeaway in a sentence.
• Add slicers. A year, a room type and a status filter would let the Director test a hunch without asking me.
• Remove the duplicate. Department revenue appears twice, once as a share on page one and once in KES on page three. Page three also has an empty lower half beside the staff table and under the payment table. A better use of that space: lost bookings by month.
• Show the trend honestly. The line shows the May and August dips clearly, which is good. Mark April as the best month and August as the worst of 2024, and shade the thin 2023 months so nobody reads them as growth.

Use the measures already built

Seven finished measures, plus the helper behind one of them, sit in the model and appear on no visual. Each answers a question the brief or the dashboard raises, and each is ready to drop onto a page:

Measure	What it would add	Value in this data
Revenue Month-over-Month Growth (with Previous Month Revenue)	The month-over-month growth the brief asked for. Put it on a column chart beside the line, with month_label on the axis.	May 2024: -37.9%. January 2024: +88.1%
Revenue Month Rank	Highlight the best and worst months without hunting along the line	April 2024 ranks 1, January 2024 ranks 2
Guest City Stay Rank	The Top 10 filter the brief asked for. Set it as a visual filter: rank less than or equal to 10.	Nairobi ranks 1
Low Rating Share	A single number for unhappy guests, to sit beside the 3.01 average	39.5%
Lost Value Share	The "13.2% of booked value" figure, labelled as such	13.2%
Room Type Revenue Share, Payment Method Revenue Share	Percent labels for the room-type bars and payment donut without relying on the visual's own percentage	Suite 34.3%, Cash 29.8%

The growth and city-rank measures are the two I would add first. Here is their code, copied from the model:

Previous Month Revenue =
VAR CurrentMonth = MAX('tembo_hotel v_clean_bookings'[stay_month])
RETURN
    CALCULATE(
        [Realised Revenue],
        REMOVEFILTERS('tembo_hotel v_clean_bookings'[stay_month]),
        REMOVEFILTERS('tembo_hotel v_clean_bookings'[month_label]),
        'tembo_hotel v_clean_bookings'[stay_month] = EDATE(CurrentMonth, -1)
    )

Revenue Month-over-Month Growth =
DIVIDE([Realised Revenue] - [Previous Month Revenue], [Previous Month Revenue])

Guest City Stay Rank =
RANKX(ALL('tembo_hotel v_clean_bookings'[guest_city]), [Completed Stays], , DESC, DENSE)

EDATE steps back one month from the month in view, so the measure works on the text month axis without a separate date table. One caution on the growth measure. In the total row, MAX(stay_month) is the last month, so the grand total would show December's growth. Put it only on a month axis, or wrap it in HASONEVALUE.

Do next time: the process

Commit the view the model depends on. The money rules live in the database view, and that was the right call. The risk is that the repository does not record how those columns are built. Put the full CREATE VIEW statement for v_clean_bookings, with realised_revenue, lost_revenue, month_label and stay_month, in the scripts folder, so a reader can rebuild the model's source from the repository alone.

Keep the reconciliation in the routine. Keep the query from section 1 in the repository and run it each time the data refreshes. If the dashboard and the query disagree, you learn that one of them is wrong before anyone else sees the page.

Guard the project file before you share it. The Power BI project stores the database host and port in plain text, and its local cache holds a copy of the data, guest names and phone numbers included. Keep the cache and local settings out of version control, and replace the host with a parameter.

Add the number a hotelier expects. An occupancy rate, nights sold against nights available, is the first figure most hoteliers look for. The brief's "occupancy" question asks which room types are booked most, and the dashboard answers that. The true rate would be a sixth card.

Test the page on a stranger. Ask someone who has never seen it to find the busiest month, the best room type and the riskiest payment method. Time them. Anything over a minute is a design bug.

What comes next

The repair list is short: fix the rank, name each loss rate, empty the donut centres, sort the bars, add slicers, and put the seven unused measures to work. After that the dashboard is ready for the question it could not yet answer, which is why bank-transfer bookings fail. That answer lives outside the data, in how the hotel takes deposits and records cancellations. The dashboard did its job when it showed where to look.

The data, the SQL and the full cleaning walk-through are in this repository. If you spot a defect I missed, tell me. A rank bug can hide on a finished page, and there may be others.