💊 How I Built an RCPA Prescription Performance Dashboard in Power BI

This content originally appeared on DEV Community and was authored by mtapa doctor

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:

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 Master with RCPA Reporting Form to enrich product info
• Created RCPA Data Table with key metrics (Brand, Doctor, Med Rep)
• Built Competitor RCPA Data Table for 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

1. Doctor Prescription Performance

   • Bar/column charts showing Rx volume per doctor vs. brand targets
   • Filterable by region and medical rep

2. Doctor Conversion Status

   • DAX logic to identify doctors meeting/exceeding targets for 3+ consecutive RCPA periods
   • Displayed with icons and color-coded status indicators

3. 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.

This content originally appeared on DEV Community and was authored by mtapa doctor