Aim
-
Evaluate key service level metrics such as LIFR %, VOFR %, OT %, IF %, and OTIF % over the six-month period.
Tools & Skills
-
Microsoft Power BI
-
Power Query
-
Data Cleaning
-
Data Analysis
-
Data Visualization
-
DAX
Analysis
Data Cleaning & Transformation
This project involved:
-
Connecting to data sources, including Excel files: dim_customers, dim_date, dim_product, dim_target_orders, and fact_order_lines.
-
Merging relevant tables, such as joining dim_customers and dim_target_orders to create a unified table with customer details and their respective service level targets.
-
Transforming the data, which included:
-
Creating time-based columns like Month, Quarter, and Week from the date field.
-
Generating new columns to classify whether each order was In Full, On Time, and On Time In Full (OTIF).
-
Data Modelling
The data model was designed using a star schema structure
DAX & Data Analysis
DAX measures were developed to calculate key service level KPIs, including:
-
LIFR % – Line Item Fill Rate: Measures the percentage of order lines delivered.
-
VOFR % – Volume Order Fill Rate: Measures the percentage of order quantity delivered.
-
IF % – In Full: Measures the percentage of orders delivered in full.
-
IT % – In Time: Measures the percentage of orders delivered in time.
-
OTIF % – On Time In Full: Measures the percentage of orders delivered on time and in full
Reporting & Visualization
The four-page report includes an Overview page and dedicated pages for Product, City, and Trend analysis.
The Overview page displays the key service level metrics—LIFR %, VOFR %, IF %, OT %, and OTIF %—alongside their comparison to company targets and/or customer-specific averages.
It also highlights how these metrics performed over the six-month period and across different product categories and cities.​​​​
The Product page showed the SLA metrics over individual products, and the city page showed the SLA metric for each customer in different cities. These pages also have slicers for product category, city, months and weeks.
​​
The trend page allow for deeper dive into trend for each category, cities for the different SLA metric. The page is powered by slicers and parameters that allows report users to investigate trends as they dim fit. ​​
Conclusion
The report reveals that AtliQ exceeded its VOFR% target, achieving 26% above the target. However, the company is underperforming in all other SLA metrics, including LIFR%, IF%, OT%, and OTIF%, indicating the need for targeted improvements in delivery performance.
Explore the Dashboard
Interact and explore the dashboard here