Recently, I completed a rewarding Power BI project that involved transforming raw Retail Chemist Prescription Audit (RCPA) data into an interactive dashboard that provides deep business insights. The challenge wasn't just in visualizing the data, but in cleaning, transforming, modeling, and telling a data-driven story that stakeholders could act upon.
In this article, I’ll walk you through how I tackled the project from start to finish, including:
- ETL in Power Query
- Data modeling and relationships
- Key DAX measures
- Designing visuals for insights
🗂️ Project Overview
Goal: Create a dynamic Power BI dashboard to analyze prescription performance by doctor, brand, region, and medical rep, and to understand doctor conversion and brand competition trends.
Key Objectives:
- Clean and transform raw RCPA data
- Build a structured data model with relationships
- Generate insightful visuals using DAX and Power BI visuals
- Help business users track brand performance and doctor behavior
📦 Dataset Summary
The dataset included four main tables:
- RCPA Reporting Form: Raw data on doctor prescriptions
- Product Master: Product and brand metadata
- Brand Targets: Expected prescription targets
- Expected Transformation Sheet: Data transformation guide
🧼 Step 1: ETL with Power Query
Using Power Query Editor, I cleaned and transformed the raw datasets into analytics-ready tables:
🔹 Cleaning Tasks:
- Removed duplicates and missing values
- Converted text-based numbers (e.g., "KSh 1,000") to numeric format
- Standardized column names and data types
🔹 Transformation Tasks:
- Merged Product Master with RCPA Reporting Form to enrich product info
- Created RCPA Data Table with relevant metrics (Brand, Doctor, Med Rep)
- Created Competitor RCPA Data Table for competitor comparisons
- Aggregated prescription counts and values as needed
This step ensured clean, structured data that could be used reliably in the data model and visuals.
🧠 Step 2: Building the Data Model
I designed a star schema where:
- Fact tables: RCPA Data and Competitor RCPA Data
- Dimension tables: Product Master and 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.
📈 Step 3: Visualizing Insights
With the model in place, I designed a clean and interactive dashboard in Power BI, which included:
🎯 Visuals Built:
1. Doctor Prescription (Rx) Performance
- Bar/Column charts to show prescription volume per doctor vs. brand targets
- Filterable by Region and Medical Rep
2. Doctor Conversion Status
- Used DAX to calculate if a doctor met or exceeded target prescriptions for 3+ consecutive RCPA periods
- Displayed with icons and color-coded status indicators
3. Brand Competition
- Stacked column charts comparing our brand’s performance against competitors
- Segmented by region and product category
Find link to project repository 👉 here
🔢 Key DAX Measures
Some example DAX measures used:
dax Total Rx = SUM('RCPA Data'[Prescription Quantity]) Target Met = IF( 'RCPA Data'[Total Rx] >= 'Brand Targets'[Target Qty], "Yes", "No" ) Doctor Conversion = // Custom logic to track 3 consecutive periods (simplified here) --- 
Top comments (0)