JCars Logistics Power BI Business Intelligence Assessment

Introduction

Jcars logistic is company dealing with cars in Kenya. The company came up with sample raw data to insight the business progress. From keeping track on the cars sold, the revenue, the car model with the higher and least purchase revenue in the company. To answer the key questions; how many cars sold in specific location, number of cars sold in total, sales rep with the most and least sales and the revenue of the cars sold; we will analyze the raw data.

Understanding the data.

Examining dataset is essential before working on it. Knowing the key aspect. Transforming my data to query after uploading to power BI. Categorizing my data from the columns and rows; transactions, people, location, vehicles, customer, measure and date.

Data cleaning

Here are the steps;

1. Promoting headers
   From the incorrect headline of my dataset. I started by promoting headers; my first row as headline to create the difference between my heading and the rest of data.

2. Changing type of different columns
   Changing the type of information in column into their unique type, example; the; text columns to text, date column to date and all numerical columns to number.

3. Numerical formatting
   The use of percentages and whole number in currency. Applying the currency ambiguity and monetary values;
   

Correcting the negative recorded revenue.
changing the currency from the USD to KES "Kenya currency."

These are few steps I took to clean my raw data and duplicate my newly cleaned data for any inconvenience later.

Data modelling

_Data modeling in Power BI is the process of organizing data from different tables and defining how those tables relate to each other, so you can analyze the data correctly and build reports and dashboards. _

Building the data

visualizing the thoughts and understanding key columns to form the fact table and the dimensional tables before generating the primary and foreign keys.

Creating the facts table and dimension table from the queries.

Starting by duplicating my cleaned data and renamed it as fact table.
After duplication I reference the new queries from the original cleaned data. Reference queries are generally more efficient, especially if you’re building layered transformations.

• Dim customers: customer name, customer type and customer age

• Dim sales rep: sales rep

• Dim location: region, county, city and branch

• Dim vehicle: vehicle type, car model, vehicle year, fuel type, transmission and color

• Dim date: order date

• Dim lead source: lead source

• Dim payment method: payment method and status
  

In each dimensional query I selected all the columns and removed the duplicates.

After the duplicate I added the index column from 1 so I can generate the primary keys.

After the process I came up with a cleaner relationship and merging of queries.

Analysis and Insights and Management tools

calculation of relevant question

key findings using Dax;

• Total cars sold =sum (fact table [unit sold]

• Total sales revenue =sum (fact table [units sold] * fact table [unit selling price]

• Total cost =sum (fact table [unit sold] * fact table [unit cost]

• Gross profit =[total sales revenue]- [total cost]

• Gross profit margin =divide ([gross profit], [total sales revenue]

My final dashboard insight

The purpose of the dashboard is to bring clear analysis vision of the key data in simple way. It's a professional way to keep track and performance.
I used table slicers and charts to easily monitor and understand the trend without the spreadsheets.

Conclusion

Understand and visualizing the data is essential before any step.it helps you create or make good decision beforehand on it. Every data has different styles. with the help of questions also matter with accordance to which Dax formula you can use.