Recently, I completed a rewarding Power BI project that transformed raw Retail Chemist Prescription Audit (RCPA) data into a dynamic, interactive dashboard. The challenge wasn’t just about visualizing metrics—it was about cleaning messy data, modeling relationships, crafting insightful DAX measures, and ultimately telling a story that stakeholders could act on.
In this article, I’ll walk you through how I approached the project from start to finish, covering:
- 🔄 ETL in Power Query
- 🧠 Data modeling and relationships
- 📊 Key DAX measures
- 🎨 Designing visuals for business insights
🗂️ Project Overview
Goal: Build a Power BI dashboard to analyze prescription performance by doctor, brand, region, and medical rep—while uncovering trends in doctor conversion and brand competition.
Key Objectives:
- Clean and transform raw RCPA data
- Build a structured data model with meaningful relationships
- Generate actionable visuals using DAX and Power BI
- Empower business users to track brand performance and doctor behavior
📦 Dataset Summary
The project was powered by four core tables:
| Table Name | Description |
|---|---|
| RCPA Reporting Form | Raw data on doctor prescriptions |
| Product Master | Metadata on products and brands |
| Brand Targets | Expected prescription targets |
| Expected Transformation Sheet | Guide for cleaning and structuring the data |
🧼 Step 1: ETL with Power Query
Using Power Query Editor, I transformed the raw inputs into analytics-ready tables.
🔹 Cleaning Tasks
- Removed duplicates and missing values
- Converted currency strings (e.g.,
"KSh 1,000") to numeric format - Standardized column names and data types
🔹 Transformation Tasks
- Merged
Product MasterwithRCPA Reporting Formto enrich product info - Created
RCPA Data Tablewith key metrics (Brand, Doctor, Med Rep) - Built
Competitor RCPA Data Tablefor comparative analysis - Aggregated prescription counts and values for performance tracking
This step laid the foundation for a reliable data model and meaningful visuals.
🧠 Step 2: Building the Data Model
I designed a star schema to ensure clarity and performance.
🔸 Fact Tables
-
RCPA Data -
Competitor RCPA Data
🔸 Dimension Tables
-
Product Master -
Brand Targets
🔁 Relationships Created
-
Product Master ➝ RCPA Data(based on product/brand) -
Brand Targets ➝ RCPA Data(to compare actual vs. target Rx) -
Product Master ➝ Competitor RCPA Data(for brand competition)
All relationships were tested and configured with correct cardinality and filter directions to ensure accurate cross-filtering.
📈 Step 3: Visualizing Insights
With the model in place, I designed a clean, interactive dashboard that delivered real business value.
🎯 Key Visuals
-
Doctor Prescription Performance
- Bar/column charts showing Rx volume per doctor vs. brand targets
- Filterable by region and medical rep
-
Doctor Conversion Status
- DAX logic to identify doctors meeting/exceeding targets for 3+ consecutive RCPA periods
- Displayed with icons and color-coded status indicators
-
Brand Competition Analysis
- Stacked column charts comparing our brand’s performance against competitors
- Segmented by region and product category
💡 Final Thoughts
This project was more than a dashboard—it was a strategic tool that helped stakeholders understand prescription dynamics, identify high-performing doctors, and assess brand competitiveness. Power BI’s flexibility, combined with thoughtful data modeling and DAX, made it possible to turn raw RCPA data into actionable insights.
Top comments (0)