Power BI Project: A Case Study of JCars Logistics

Introduction

JCars Logistics is a car sales business. The business serves a diverse range of clients including, individuals, corporates, and even the government. Jcars has 8 branches stocking different car makes. The business has 10 sales representatives tasked with driving sales across the 8 branches while leveraging different lead sources such as social media. For this analysis, the goal was to create diverse KPIs and helping the business gain a deeper understanding of how the business is performing. The analysis was a gradual process that yielded meaningful insights for the business.

Data Cleaning

The availed raw facts table was quite messy. The table contained erroneous data types, a mix of currencies, mixed date formats, and a mix of upper- and lower-case characters. Such mix-ups necessitated cleaning and preparation prior to performing subsequent analysis.

The first step of cleaning and preparing the data entailed loading it to Power BI and creating column headers.

Cleaning Text Columns

J Cars facts table had the following text columns:

• Order ID
• Customer Name
• Customer Type
• Region
• County
• City
• Branch
• Sales Rep
• Lead Source
• Car Make
• Car Model
• Vehicle Type
• Vehicle Year
• Fuel Type
• Transmission
• Colour
• Payment Method
• Payment Status
• Delivery Status
• Returned

All these text columns shared similar problems such as a mix of characters, missing values, misspellings and word shortforms as well as presence of hyphens, “N/A” and “missing” in cells that ought to have been left blank. Cleaning was done for each column individually and entailed:
Trimming

Most of the text had leading, trailing, and excessive internal spaces. This was corrected by highlighting a column, right-clicking on it and then selecting “trim” from the “transform” button.

Transforming Text to Proper Case

Capitalizing each word was crucial in eliminating a mix of upper- and lower-case characters. It was especially instrumental in writing people’s and places’ names in columns such as “Sales Rep”, “Customer Name”, “County” and “City” correctly.
Replacing Values

Replacing values was crucial in correcting misspelt words and getting rid of unnecessary texts or punctuation marks. For instance, the approach was used in correcting misspelling in the name “Kev1N”, and replacing texts such as “missing” with blanks or “Unknown”.

Cleaning Date Columns

JCars facts table had the following date columns:

• Order Date
• Delivery Date

The date columns were a mix of different date formats including, Excel date serials, MM/DD/YYYY DD/MM/YYYY and YYYY-MM-DD. For some, the month name was also included instead of the month number. With the dates appearing like that, it would be impossible to perform analysis involving dates, such as trends and changes over time. As such, it was important to alter the dates to a common date format.
The first step involved converting the Excel date serials to MM/DD/YYYY format. Here, I created a custom column named “Custom Order Date” using the formula below:
Custom Order Date
= if Text.Length ([Order Date]) = 5
then Date.From(Number.FromText([Order Date]))
else [Order Date]

The formula extracted the 5-digit Excel date serials stored as text and converted them to nullable dates whilst retaining everything else as it was in the original date column.

From here, I converted the rest of the dates to the DD/MM/YYYY format using locale. The output was as below:

The errors represented invalid dates such as 2026-13-04. Since we do not have a 13th month, Power BI detects such as errors.
I then replaced the errors with nulls as shown below:

The final output was as shown below:

The same approach was applied on the delivery date column. In the end, the new order date and delivery date columns looked like this:

Cleaning Numeric Columns

JCars facts table had the following numeric columns:

• Customer Age
• Units Sold
• Discount %
• Customer Rating
• Review Count

Cleaning the numeric columns entailed removing unnecessary text such as “out of 5” in the “Customer Rating” column, replacing words with their numeric equivalents (for example replacing “ten” with 10), replacing texts such as “unknown” with blank, and selecting the data type as decimal number. As such, replacing values was one of the most repeated steps in cleaning numeric columns.
However, cleaning the discount column required a different approach. The column contained glaring issues that needed fixing prior to rendering it useful for analysis.

Unnecessary texts and punctuations such as hyphens, “NULL”, and “disc” were expunged by replacing them with blanks. However, “No discount” was replaced with a zero. Erroneous entries such as percentages greater than 100% and whole numbers equal or greater than 1 were replaced with nulls because it is not practically possible to offer 100% or greater discount on a car. Next, I got rid of the % sign. After getting rid of the unnecessary texts and erroneous discount percentages, the discount column only contained numerals ranging from zero to figures below 100. The figures needed further cleaning. Any figure greater than 1 had to be converted to a decimal. This effectively changed figures that were initially recorded as 3%, for instance, to 0.03 whilst retaining those that were originally recorded as decimals. For this step, I created a custom column using M language as shown below:
Custom Discount %
= if [Discount] >= 1 then [Discount]/100
else [Discount]


The formula converted all numerical figures to decimals. Blank cells returned errors. As the final step, I replaced the errors with null. The final output was as shown below:

Cleaning Currency Columns

JCars facts table had the following currency columns:

• Unit Selling Price
• Unit Cost
• Delivery Fee
• Logistics Cost
• Revenue Recorded

Currency columns contained a host of erroneous entries. Therefore, cleaning of the columns was done systematically. First, entries such as “#VALUE!”, “ERROR”, “KES”, “KSh”, hyphens, “missing”, “TBD”, and “not available” were replaced with blanks. Next, I replaced the abbreviations “EUR” and “USD” with euro and dollar signs, respectively. I also assumed that figures with a prefix “?” were meant to be represented in the dollar denomination. As such, I replaced every “?” prefix with a dollar sign. The next step involved multiplying figures recorded in millions (for example 1.9M) with 1000000 for uniformity with other currency values represented in Kenya shillings. Additionally, I had to convert figures in euros, US dollars, and the South African rand by multiplying the relevant figures with 148, 130, and 8, where the figures 148, 130, and 8 represent the exchange rates. To do this, I used the formula below:

Custom Unit Cost = 
if Text.Contains([Unit Cost], "$") 
then Number.FromText(Text.Remove([Unit Cost], {"$", ","})) * 130

else if Text.Contains([Unit Cost], "ZAR") 
then Number.FromText(Text.Remove([Unit Cost], {"ZAR", ","})) * 8

else if Text.Contains([Unit Cost], "€") 
then Number.FromText(Text.Remove([Unit Cost], {"€", ","})) * 148

else if Text.Contains([Unit Cost], "M") 
then Number.FromText(Text.Remove([Unit Cost], {"€", ","})) * 1000000

else [Unit Cost]

Next, any errors were replaced with nulls. The approach was repeated for all currency columns and the end result was:

Creation of Dimension Tables

After cleaning the raw JCars flat table, I created 4 dimensional tables for orders, branches, customers, and sales rep.
Creating Orders Dimension Table

• Created a duplicate of the JCars flat table and named it “dim_orders”.
• Selected “Order ID”, “Order Date”, and “Delivery Date” columns.
• Removed other columns.

Creating Branches Dimension Table

• Created a duplicate of the JCars flat table and named it “dim_branches”.
• Selected “Branch” column.
• Removed other columns.
• Removed duplicates and was left with 8 rows.

• Added a custom index column with the starting index being 100 and an increment of 1 and renamed it to “Branch ID”.
• Reordered the columns as shown below:

Creating Sales Rep Dimension Table

• Created a duplicate of the J Cars flat table and named it “dim_sales_rep”.
• Selected “Sales Rep” column.
• Removed other columns.
• Removed duplicates and was left with 10 rows.

• Added an index column with the starting from 1 and renamed it to “Sales Rep ID.
• Reordered the columns as below:

Creating Customers Dimension Table

• Created a duplicate of the J Cars flat table and named it “dim_customers”.
• Selected “Customer Name”, “Customer Type”, “Customer Age”, “Region”, “County”, and “City” columns.
• Removed other columns.
• Removed duplicates and was left with 276 rows. No row was deleted because no entry was an exact duplicate of any other based on similarities on all 6 columns.
• Added an index column starting from 1 and renamed it to “Customer ID”.
• Reordered the columns as below:

Creating the Final Facts Table
Creating the final facts table named “facts_jcars” entailed:

1. Inserting “Customer ID”, “Branch ID”, and “Sales Rep ID” columns in the facts table.
2. Removing columns in the facts table that were also present in individual dimension tables

Inserting the ID columns was a step-wise procedure involving merging queries whereby the facts table was selected as the left table and appropriate dimension tables selected as the right tables. The tables were then joined using the left outer join and selecting matching columns. Finally, the joint table was expanded by keeping only the columns needed. In the end, the following facts table was arrived at:

Data Modelling

I created a star schema whereby the facts table (“facts_jcars”) was surrounded by the four dimension tables (“dim_customers”, “dim_orders”, “dim_branches”, “dim_sales_rep” as shown below:

Creating the relationships was crucial for allowing the separate tables to function together as a single unified field. Additionally, the relationships would allow accurate filtering and correct aggregation.

Creation of DAX

Calculated Column
Discounted Selling Price = facts_jcars[Unit Selling Price] * (1- facts_jcars[Discount as %])

Calculated Measures

Payment Cancellations = CALCULATE(COUNTROWS(facts_jcars), facts_jcars[Payment Status] = "Cancelled")

Total Returned = CALCULATE(COUNTROWS(facts_jcars), facts_jcars[Returned] = "Yes")

Total Cars Sold = (SUM(facts_jcars[Units Sold]) - [Total Returned])

Total Cost = SUMX(facts_jcars, facts_jcars[Unit Cost] + facts_jcars[Delivery Fee KSh] + facts_jcars[Logistics Cost KSh])

Total Delivery Fees = SUM(facts_jcars[Delivery Fee KSh])

Total Logistics Cost = SUM(facts_jcars[Logistics Cost KSh])

Total Profit = SUMX(facts_jcars, facts_jcars[Revenue Recorded KSh] - facts_jcars[Unit Cost] - facts_jcars[Delivery Fee KSh] - facts_jcars[Logistics Cost KSh])

Total Revenue = SUM(facts_jcars[Revenue Recorded KSh])

Total Unit Cost = SUM(facts_jcars[Unit Cost])

Analysis

The analysis bit entailed creation of visual that answered different business questions and provided meaningful insights. Here I created a map visual to show revenue generation per county, a line graph to show monthly total revenue and profit, a bar chart to show revenue generated by each sales representative, a donut chart to compare revue generated by manual cars against that generated by automatic ones, a line and column chart to show how revenue and profit varied by car type, and a bar chart to show how different car makes were rated by customers.

Dashboard Development

The dashboard comprised KPIs, slicers, and visuals showing how the business was performing.

Insights

• Kakamega is the leading region in terms of revenue.
• Toyota generates highest revenue.
• Automatic cars generate higher returns compared to manual ones.
• Profit and revenue were at the highest in April.
• Dealers and the government are among the most important customers in terms of revenue.
• Honda is among the most returned car type and has a low average rating too.

• Top 3 sales representatives:

  • Faith Achieng
  • Mary Wanjiku
  • Brian Otieno

Recommendations

• Investigate why sales rep Daniel Kimani ranks among bottom 3 in terms of revenue generation but highest in terms of payment cancellations. Could he need further training or replacement?
• Investigate why Honda has high return rate, low average rating, and is among low revenue generators. Could it be an issue of car quality?