Cortonis Pharma is a fictional pharmaceutical company operating across Poland and Germany. The dataset covers 254,000 sales transactions across 4 years (2017–2020), including product-level detail, customer and distributor information, channel breakdown, geographic data, and sales team hierarchy.
The dashboard is designed for two audiences:
- Sales leadership - executive overview, trend monitoring, high-level KPIs
- Territory and field managers - granular performance by rep, territory, product, and channel
All pages were wireframed in Figma before development - the wireframes are kept in dashboard/assets/wireframes/ and drove the final page layouts.
Foresight - Pharmaceutical Manufacturing Company's Wholesale-Retail Data 254,082 transactions · Poland & Germany · 2017–2020 foresightbi.com.ng/practice-data
| Field | Description |
|---|---|
Distributor |
Wholesaler name |
Customer Name |
Pharmacy or hospital name |
City / Country |
Customer location |
Channel |
Hospital or Pharmacy |
Sub-channel |
Private, Retail, Institution, Government |
Product Name |
Drug name |
Product Class |
Therapeutic class (Antibiotics, Analgesics, Mood Stabilizers, Antiseptics, Antipiretics, Antimalarial) |
Quantity / Price / Sales |
Transaction volume and value |
Month / Year |
Transaction period |
Name of Sales Rep |
Rep who facilitated the sale |
Manager / Sales Team |
Team hierarchy (Alfa, Bravo, Charlie, Delta) |
Note on Poland data: sales data for Poland is available for 2018 only. YoY variance indicators are intentionally hidden for Poland to avoid misleading comparisons.
- Page 1 - Executive Overview
- Page 2 - Territory & Geographic Performance
- Page 3 - Product Mix
- Page 4 - Channel & Customer Analysis
- Page 5 - Sales Rep & Team Performance
- Design Approach
- Key DAX Measures
- Repository Structure
- Opening the Project
- How is the business performing overall - in sales, volume, orders, pricing, and customer count?
- Is performance improving or declining vs. the previous year?
- What does the sales trend look like across the year, and how does it vary by quarter?
- Which territories, products, channels, or teams are driving the most value?
Five top-level metrics, each showing the current period value alongside the previous year's value and variance in both absolute and percentage terms (color-coded green/red):
| KPI | Measure |
|---|---|
| Sales | 1. Total_Sales |
| Units Sold | 1. Total_Unit |
| Orders | 1. Total_Order |
| Price / Unit on Average | 1. Avg_Price/Unit |
| Customers | 1. Active_Customers |
Each card displays: current value · last year value (2. Total_Sale_Last_Year) · absolute variance · % variance (4. Delta%_Total_Sales). Showing both absolute and relative variance is a deliberate choice - on billion-dollar figures, a "-16%" means more when paired with "-$575.96M".
The chart overlays two series simultaneously:
- Current period -
1. Total_Sales - Same period last year -
2. Total_Sale_Last_Year
A dropdown field parameter (Executive Overview Parameter) lets the user switch the displayed metric across all five KPIs. Switching updates both the trend chart and the distribution matrix simultaneously - a single selection drives the entire page.
Parameter 1 - Metric (Executive Overview Parameter): Sales, Units, Orders, Price/Unit, Customers
Parameter 2 - Dimension (Dimension Categories): switches the breakdown axis between:
- Locations (Country → Region → City)
- Product Class → Product
- Channel → Sub-channel
- Sales Team → Sales Rep
The matrix displays: absolute value · data bar · 4. Delta%_Total_Sales (Var% YoY) - color-coded green/red.
A cross-dimension text slicer on a concatenated Search column combining all key dimensions into a single searchable string per row.
Six synchronized slicers on the left sidebar:
Date · Product Class / Product · Channel / Sub-channel · Country / Region / City · Distributor · Sales Team / Rep
A Clear All button resets all slicers via a named bookmark.
- Personalized greeting -
USERPRINCIPALNAME()renders the logged-in user's name and email - Date range slicer - dual-handle slider with explicit start/end dates
- Last updated timestamp - displayed in the sidebar
- Help links - sidebar footer: How to use · Contact us · About
- Custom navigation - page buttons rather than native Power BI tabs
- Tooltip pages - hovering surfaces contextual detail
- Author signature - "Developed by Guillaume Pien"
- Where are we growing and where are we losing ground?
- How do Germany and Poland compare across all key metrics?
- Which regions and cities are the strongest performers - and which are declining?
- Which cities are high-volume but low-growth (defend), and which are low-volume but high-growth (accelerate)?
Two dedicated cards - Germany and Poland - displaying all five metrics with YoY variance. Poland YoY indicators display "--" (2018 data only).
Bubble map (map visual) with City as location, 1. Total_Sales as bubble size, switchable by Executive Overview Parameter.
Same dual field parameter architecture as Page 1. Defaults to Country → Region → City.
| Axis | Measure |
|---|---|
| X | 1. Total_Sales |
| Y | 5. Delta%_Total_Sales_NonFormatted |
| Size | 1. Total_Unit |
| X reference line | Median Sales by Region |
| Y reference line | Median Growth by Region |
Quadrant logic - BCG Matrix framework:
| Quadrant | Label | Strategic implication |
|---|---|---|
| Top right | ⭐ Stars | Protect and invest |
| Top left | ❓ Question Marks | Evaluate and accelerate |
| Bottom right | 🐄 Cash Cows | Defend and harvest |
| Bottom left | 🐕 Dogs | Review and deprioritize |
Reference lines use dynamic DAX medians - recalculated on every filter change. Quadrant background colors (blue = growth zone, pink = decline zone) are applied via a custom legend image. Zoom sliders on both axes allow isolation of the dense city cluster.
- What are we selling, and is the mix shifting across quarters?
- Which therapeutic classes drive the most revenue - and are they growing?
- Which classes have the strongest seasonal patterns, and when do they peak?
All 6 therapeutic classes ranked by quarter, piloted by Executive Overview Parameter. Ribbon crossings signal ranking changes between classes.
Color convention: coordinated blue/teal palette - green (#03DE74) and red (#D7263D) are excluded, reserved for variance indicators only.
| Class | Color |
|---|---|
| Analgesics | #0B1F52 |
| Antibiotics | #58FFE6 |
| Antimalarial | #55FFCC |
| Antiseptics | #1A6B8A |
| Antipiretics | #4B5EA6 |
| Mood Stabilizers | #00A896 |
Product Class → Product Name hierarchy, same dual field parameter as all other pages.
| Role | Measure / Field |
|---|---|
| X axis | MonthName |
| Small multiples | Product Class |
| Main line | Seasonality_Index |
| Upper band | Seasonality_Upper_95 |
| Lower band | Seasonality_Lower_95 |
Seasonality Index: Monthly Total / Average Monthly Total (annual) - normalized so all classes are comparable regardless of absolute volume. Reference line at Y=1.0 materializes the annual average baseline.
95% Confidence Intervals: Index ± 1.96 × (StdDev / √N) where StdDev is the dispersion across products within the class for that month.
Seasonal patterns:
| Class | Peak | Interpretation |
|---|---|---|
| Analgesics | Jun–Aug | Estival - sports injuries, outdoor activity |
| Antibiotics | Jan–Feb | Winter - respiratory infections |
| Antimalarial | Apr & Oct | Two peaks - travel seasons |
| Antipiretics | Feb & Nov | Winter - fever and infections |
| Antiseptics | Flat | Low seasonality - regular year-round usage |
| Mood Stabilizers | Complex | Consistent with seasonal depression literature |
- Who is buying and through what channel?
- How do Hospital and Pharmacy compare across all key metrics?
- Which sub-channel drives the most value for each therapeutic class?
- Where does revenue concentrate when drilling from channel down to city level?
Two cards - Hospital and Pharmacy - with all five metrics and full YoY variance. Same measure set as all other cards (1. Total_Sales, 4. Delta%_Total_Sales, etc.).
A matrix visual crossing Channel × Sub-channel against Product Class, displaying 1. Total_Sales with conditional color formatting - gradient from light to dark by relative value within each column.
Fields: Channel · Sub-channel · Product Class × Executive Overview Parameter metric
Sub-title: "Which sub-channel drives the most value for each therapeutic class?"
Drill-down hierarchy:
1. Total_Sales → Channel → Sub-channel → Product Class → City
Piloted by Executive Overview Parameter - the metric switches dynamically. The user drills through each level by clicking, with each branch showing the absolute contribution to the parent node.
Sub-title: "Drill into any metric from Channel down to City level"
- Who truly outperforms - in their team and across the board?
- Which teams are driving the most revenue, and at what growth rate?
- Is a rep's performance driven by genuine skill or by territory advantage?
Four cards - Alpha, Beta, Charlie, Delta - with all five metrics and YoY variance. Same measure set as all other cards.
| Team | Sales | Growth YoY |
|---|---|---|
| Alpha | $777.13M | ▲ +23.0% |
| Beta | $825.35M | ▲ +29.3% |
| Charlie | $806.22M | ▲ +50.0% |
| Delta | $1.10bn | ▲ +22.8% |
Sales Team → Name of Sales Rep hierarchy with six performance columns, all piloted by Executive Overview Parameter:
| Column | Measure | Description |
|---|---|---|
| Sales / Units | 1. Total_Sales / 1. Total_Unit |
Absolute value + data bar + Var% YoY |
| vs Team Avg | 6. Total_Unit_Rep_vs_TeamAvg |
% deviation from rep's own team average |
| Team Rank | 7. Total_Unit_Rep_Rank_InTeam |
Rank within the rep's team |
| vs Global Avg | 8. Total_Unit_Rep_vs_GlobalAvg_Sales |
% deviation from all-rep average |
| Global Rank | 9. Total_Units_Rep_Rank_Global |
Rank across all reps company-wide |
Why two ranking dimensions matter
A raw sales ranking is misleading - a rep covering a major city will structurally outsell a rural rep regardless of skill. The dual ranking separates absolute performance (Global Rank) from contextual performance (Team Rank + vs Team Avg):
- High global + high team rank → genuine top performer
- High global + low team rank → strong territory, weaker relative performance
- Low global + high team rank → strong performer in a weaker territory
Example: Abigail Thompson (Bravo) is #1 in her team (+10.6% vs team avg) AND #1 globally (+12.8% vs global avg). Alan Ray (Alfa) is #3 in his team (-7.2%) and #12 globally (-10.9%) - structurally disadvantaged or underperforming.
Key DAX patterns
ISINSCOPE suppresses subtotals. SUM > 0 guard prevents phantom rows for reps outside their actual team. ALL(Dim_Sales_Team) breaks the team filter context for global measures:
Rep_Rank_Global =
IF(
ISINSCOPE(Fact_Sales[Name of Sales Rep]) &&
CALCULATE(SUM(Fact_Sales[Sales])) > 0,
VAR CurrentRepSales = CALCULATE(SUM(Fact_Sales[Sales]))
VAR AllRepsSales =
CALCULATETABLE(
ADDCOLUMNS(
ALL(Fact_Sales[Name of Sales Rep]),
"RepSales",
CALCULATE(SUM(Fact_Sales[Sales]), ALL(Dim_Sales_Team))
),
ALL(Dim_Sales_Team)
)
RETURN
COUNTROWS(FILTER(AllRepsSales, [RepSales] > CurrentRepSales)) + 1,
BLANK()
)
Rep_vs_TeamAvg =
IF(
ISINSCOPE(Fact_Sales[Name of Sales Rep]) &&
CALCULATE(SUM(Fact_Sales[Sales])) > 0,
VAR CurrentTeam = MAX(Fact_Sales[Sales Team])
VAR RepSales = CALCULATE(SUM(Fact_Sales[Sales]))
VAR TeamAvg =
CALCULATE(
AVERAGEX(
VALUES(Fact_Sales[Name of Sales Rep]),
CALCULATE(SUM(Fact_Sales[Sales]))
),
ALL(Fact_Sales[Name of Sales Rep]),
Fact_Sales[Sales Team] = CurrentTeam
)
RETURN DIVIDE(RepSales - TeamAvg, TeamAvg),
BLANK()
)
Wireframe-first: Every page was designed in Figma before any Power BI development - layout, color, hierarchy, and slicer placement were all locked before touching the tool. The wireframes were embedded as reference layers during development, then replaced by the final polished page backgrounds (dashboard/assets/backgrounds/); the original wireframes are archived in dashboard/assets/wireframes/.
Why UX matters in BI
Users today are surrounded by polished consumer apps. When an internal tool doesn't meet that standard, adoption suffers - not because the data is wrong, but because people don't have time to relearn navigation. This dashboard is built to feel as intuitive as the apps people already use daily: consistent navigation, clear visual hierarchy, and each page designed for a specific audience and a single analytical question.
Typography
| Role | Font |
|---|---|
| Titles & headers | Trebuchet MS |
| Body & data labels | Segoe UI |
Color palette
| Color | Hex | Semantic role |
|---|---|---|
| Emerald Green | #03DE74 |
Positive variance only |
| Navy | #0B1F52 |
Sidebar, primary dark elements |
| Cyan | #58FFE6 |
Data bars, chart fills |
| Mint | #55FFCC |
Secondary accent |
| Red | #D7263D |
Negative variance only |
Green and red are reserved exclusively for variance indicators - never used decoratively.
Additional colors for therapeutic class ribbon chart:
| Class | Hex |
|---|---|
| Antiseptics | #1A6B8A |
| Antipiretics | #4B5EA6 |
| Mood Stabilizers | #00A896 |
YoY variance
4. Delta%_Total_Sales =
VAR CurrentSales = [1. Total_Sales]
VAR PreviousSales = [2. Total_Sale_Last_Year]
RETURN DIVIDE(CurrentSales - PreviousSales, PreviousSales)
Dynamic median for scatter quadrant
Median Sales by Region =
MEDIANX(VALUES(Fact_Sales[City]), CALCULATE([1. Total_Sales]))
Seasonality Index
Seasonality_Index =
VAR TotalThisMonth =
CALCULATE(
SUM(Fact_Sales[Sales]),
REMOVEFILTERS('Calendar'[Date]),
REMOVEFILTERS('Calendar'[Year]),
REMOVEFILTERS('Calendar'[MonthName])
)
VAR TotalAllYear =
CALCULATE(SUM(Fact_Sales[Sales]), REMOVEFILTERS('Calendar'))
VAR NbMonths =
CALCULATE(DISTINCTCOUNT('Calendar'[Month Number]), REMOVEFILTERS('Calendar'))
RETURN
DIVIDE(TotalThisMonth, DIVIDE(TotalAllYear, NbMonths))
Seasonality 95% CI
Seasonality_Upper_95 =
VAR AvgIndex = [Seasonality_Index]
VAR StdDev =
CALCULATE(
STDEVX.P(VALUES(Fact_Sales[Product Name]), CALCULATE([Seasonality_Index])),
REMOVEFILTERS('Calendar'[Date]),
REMOVEFILTERS('Calendar'[Year]),
REMOVEFILTERS('Calendar'[MonthName])
)
VAR N =
CALCULATE(
DISTINCTCOUNT(Fact_Sales[Product Name]),
REMOVEFILTERS('Calendar'[Date]),
REMOVEFILTERS('Calendar'[Year]),
REMOVEFILTERS('Calendar'[MonthName])
)
RETURN AvgIndex + 1.96 * DIVIDE(StdDev, SQRT(N))
This repo follows the BI Repository Template layout:
Cortonis-Pharma-Sales-Dashboard/
├── dashboard/
│ ├── powerbi/
│ │ ├── Cortonis Sales Dashboard.pbip # Power BI Project entry point - open this file
│ │ ├── Cortonis Sales Dashboard.Report/ # Report definition (pages, visuals, bookmarks)
│ │ └── Cortonis Sales Dashboard.SemanticModel/ # Data model, relationships, DAX measures (TMDL)
│ └── assets/ # Backgrounds, icons, theme, Figma wireframes
├── data/
│ ├── raw/ # Working copies of the source dataset (Excel + CSV)
│ ├── processed/ # Unused - shaping happens in the BigQuery views
│ └── sample/ # Anonymized/example data
├── docs/ # Data dictionary, methodology
├── reports/
│ ├── screenshots/ # Final page captures (PNG)
│ └── validation/ # DAX measure validation exports
├── sql/
│ └── views/ # BigQuery views the semantic model reads (fact + dimensions)
├── notebooks/ · scripts/ · src/ # Unused for this Power BI-only project - template scaffold
├── CHANGELOG.md · LICENSE
└── README.md
The report ships as a PBIP (Power BI Project) rather than a single .pbix - the model (TMDL) and report definition are stored as plain text, which makes the DAX measures and page layout diffable and reviewable directly on GitHub.
| Location | Description |
|---|---|
dashboard/powerbi/*.pbip |
Project file - open this in Power BI Desktop |
dashboard/powerbi/*.SemanticModel/definition/*.tmdl |
Tables, relationships, and every DAX measure in plain text |
data/raw/Pharm Data (Data).csv |
Full 254,082-row source dataset (working copy - the live model queries BigQuery, seedocs/methodology.md) |
reports/screenshots/*.PNG |
Full-page captures referenced throughout this README |
dashboard/assets/wireframes/ |
Wireframes designed before development |
docs/ |
Data dictionary and methodology |
- Install Power BI Desktop (December 2023 release or later - PBIP support ships by default from that version on).
- Open
dashboard/powerbi/Cortonis Sales Dashboard.pbipdirectly - Power BI Desktop will load the semantic model and report together. - On first load, Power BI needs to re-establish the BigQuery connection (or point it at
data/raw/Pharm Data (Data).csvas an offline substitute) if prompted.
This project is licensed under the MIT License - see LICENSE.
Built as Project 1 of a two-part pharma commercial analytics series. Project 2 - Advanced Analytics Layer extends the analysis with Python-based customer segmentation, territory underperformance modeling, and sales forecasting.