How Pharmacies Can Improve Sales and Inventory Using Power BI Dashboards

Posted on May 01, 2026

Running a pharmacy without clear visibility into sales, profit, inventory, and store performance is like managing prescriptions without knowing stock levels.

Many pharmacies lose revenue, not because demand is low, but because reporting is delayed, inventory decisions are reactive, and leadership lacks real-time insights.

A well-designed pharmacy performance dashboard solves this problem.

This blog explains how a Power BI pharmacy sales analytics dashboard is structured, which KPIs matter most, how the data model works, and how it enables pharmacies to improve revenue, margins, and operational efficiency.

If you're exploring a ready-to-use healthcare Power BI template, this guide will help you fully understand its capabilities before accessing the complete solution.

Why Pharmacies Need a Power BI Dashboard

Pharmacy operations involve multiple moving parts that require continuous monitoring:

Sales across multiple locations
Generic vs branded medicine performance
Revenue vs target achievement
Promotional sales effectiveness
Cost and margin optimization
Pharmacy health score tracking
Underperforming locations
Product-level profitability

Without a centralized dashboard, these insights remain scattered across spreadsheets and disconnected systems.

A Power BI pharmacy dashboard transforms this complexity into a single, real-time decision-making system.

Explore Before You Decide

How Pharmacies Can Improve Sales and Inventory Using Power BI Dashboards Click here to Access the live dashboard preview to understand its full structure, KPIs, and insights in action.

How This Pharmacy Sales Analytics Dashboard Is Built

This dashboard is designed for both executive-level decision-making and daily operational visibility.

It combines:

Revenue analysis
Profit tracking
Cost monitoring
Promotional sales analytics
Pharmacy health score analysis
Multi-location reporting
Target vs achievement tracking

The core objective:

Turn raw pharmacy data into actionable decisions.

With this dashboard, decision-makers can instantly identify:

Underperforming pharmacy locations
High-margin products
Rising cost areas
Locations requiring immediate intervention
Impact of promotional campaigns

This makes it a powerful pharmacy profit and performance optimization tool.

See the Dashboard in Action

Get a complete walkthrough of all report pages, KPIs, and filters through the live preview before accessing the full template.

Data Model Structure Behind the Dashboard

A high-performance dashboard starts with a strong data model.

This solution uses a clean star schema, ensuring scalability, speed, and accurate calculations.

Main Tables Used

Fact Table – FactSales

Includes:

Revenue
Cost
Margin
Units Sold
Sales Orders
Promo Flag

This acts as the core transactional engine.

Dimension Tables

DimDate

Year, Quarter, Month
Trend analysis
MoM & YoY calculations

DimPharmacy

Location hierarchy (Country → Region → City)
Store type & size
Pharmacy segmentation

DimProduct

Category & brand
Generic vs branded analysis
Product-level profitability

Targets Table

Sales targets
Profit targets
Segment-based comparisons

This enables target vs performance tracking.

Relationship Model

FactSales[Date] → DimDate[Date]
FactSales[PharmacyID] → DimPharmacy[PharmacyID]
FactSales[ProductID] → DimProduct[ProductID]
Targets Table[PharmacySegment] → DimSegment[PharmacySegment]
Targets Table[Target Date] → DimDate[Date]

This structure improves model speed and ensures reliable KPI calculations.

Core KPI Measures Used

The dashboard includes structured DAX measures across key areas:

Cost Measures – Track operational costs with trends
Health Score Measures – Evaluate pharmacy performance (0–100 scale)
Orders Measures – Monitor sales volume and demand
Profit Measures – Analyze profitability vs targets
Promo Measures – Measure campaign effectiveness
Revenue Measures – Track growth and achievement
Unit Measures – Analyze product movement
Additional Measures – Rankings, category insights, and commentary

Want to Explore These Metrics Visually?

Interact with the full dashboard to see how each KPI behaves across locations, products, and time.

Calculates total cost by summing CostEUR from fact table.

Total Cost =
SUM ( FactSales[CostEUR] )

Returns total cost for the same period in the previous year when a single year is selected.

Previous Year Cost =
IF (
    HASONEVALUE ( DimDate[Year] ),
    CALCULATE ( [Total Cost], DATEADD ( DimDate[Date], -1, YEAR ) )
)

Returns absolute change in cost from previous month with +/- symbol.

Cost MoM Value =
VAR HasOneMonth =
    HASONEVALUE ( 'DimDate'[Year Month] )
VAR CurrentCost = [Total Cost]
VAR PreviousCost =
    CALCULATE ( [Total Cost], DATEADD ( 'DimDate'[Date], -1, MONTH ) )
VAR MoMChange = CurrentCost - PreviousCost
RETURN
    IF (
        NOT HasOneMonth || ISBLANK ( PreviousCost ),
        "--",
        IF (
            MoMChange > 0,
            "+" & FORMAT ( MoMChange, "#,##0" ),
            "-" & FORMAT ( ABS ( MoMChange ), "#,##0" )
        )
    )

Returns formatted percentage change with up/down arrows and previous month label.

Cost MoM % =
VAR HasOneMonth =
    HASONEVALUE ( 'DimDate'[Year Month] )
VAR CurrentCost = [Total Cost]
VAR PreviousCost =
    CALCULATE ( [Total Cost], DATEADD ( 'DimDate'[Date], -1, MONTH ) )
VAR MoMChange = CurrentCost - PreviousCost
VAR MoMPercent =
    DIVIDE ( MoMChange, PreviousCost )
VAR PrevMonthLabel =
    FORMAT (
        CALCULATE ( MAX ( 'DimDate'[Date] ), DATEADD ( 'DimDate'[Date], -1, MONTH ) ),
        "MMM-yyyy"
    )
RETURN
    IF (
        NOT HasOneMonth || ISBLANK ( PreviousCost ),
        "--",
        IF (
            MoMChange > 0,
            "▲ " & FORMAT ( MoMPercent, "0.0%" ) & " vs " & PrevMonthLabel,
            "▼ " & FORMAT ( ABS ( MoMPercent ), "0.0%" ) & " vs " & PrevMonthLabel
        )
    )

Returns raw decimal value of month-over-month cost percentage change.

Cost MoM % 2 =
VAR HasOneMonth =
    HASONEVALUE ( 'DimDate'[Year Month] )
VAR CurrentCost = [Total Cost]
VAR PreviousCost =
    CALCULATE ( [Total Cost], DATEADD ( 'DimDate'[Date], -1, MONTH ) )
VAR MoMChange = CurrentCost - PreviousCost
VAR MoMPercent =
    DIVIDE ( MoMChange, PreviousCost )
RETURN
    IF ( NOT HasOneMonth || ISBLANK ( PreviousCost ), "--", MoMPercent )

Returns color code: blue for cost decrease/stable, red for cost increase.

Cost MoM Color =
VAR PreviousCost =
    CALCULATE ( [Total Cost], DATEADD ( 'DimDate'[Date], -1, MONTH ) )
VAR Change = [Total Cost] - PreviousCost
RETURN
    IF (
        ISBLANK ( PreviousCost ),
        BLANK (),
        IF ( Change <= 0, "#3D90D7", "#DC2626" )
    )

Returns background color: light red for cost increase, light blue for cost decrease.

Cost MoM Bg Color =
VAR HasOneMonth =
    HASONEVALUE ( 'DimDate'[Year Month] )
VAR CurrentCost = [Total Cost]
VAR PreviousCost =
    CALCULATE ( [Total Cost], DATEADD ( 'DimDate'[Date], -1, MONTH ) )
VAR MoMChange = CurrentCost - PreviousCost
RETURN
    IF (
        NOT HasOneMonth || ISBLANK ( PreviousCost ),
        "",
        IF ( MoMChange > 0, "#F7CACA", "#AFDDFF" )
    )

Calculates pharmacy health score (0-100) based on sales achievement (40pts max), profit achievement (40pts max), and profit margin (20pts max).

Health Score =
VAR SalesAchievement =
    DIVIDE ( [Total Sales], [Sales Target], 0 )
VAR ProfitAchievement =
    DIVIDE ( [Total Profit], [Profit Target], 0 )
VAR ProfitMargin =
    DIVIDE ( [Total Profit], [Total Sales], 0 )
VAR SalesScore =
    MIN ( SalesAchievement * 40, 40 )
VAR ProfitScore =
    MIN ( ProfitAchievement * 40, 40 )
VAR MarginScore =
    MIN ( ProfitMargin * 100, 20 )
RETURN
    ROUND ( SalesScore + ProfitScore + MarginScore, 0 )

Classifies health score into 5 performance tiers with visual indicators.

Health Status =
SWITCH (
    TRUE (),
    [Health Score] >= 90, "▲ Excellent",
    [Health Score] >= 75, "● Good",
    [Health Score] >= 60, "■ Average",
    [Health Score] >= 40, "▼ Below Average",
    "▼ Poor"
)

Returns color code for each health status level.

Health Status Color =
SWITCH (
    TRUE (),
    [Health Score] >= 90, "#AFDDFF",
    [Health Score] >= 75, "#CAF7CE",
    [Health Score] >= 60, "#F0E199",
    [Health Score] >= 40, "#F7CACA",
    "#F7CACA"
)

Calculates average health score across all pharmacies.

Average Health Score =
AVERAGEX ( VALUES ( DimPharmacy[PharmacyID] ), [Health Score] )

Returns median health score across all pharmacies.

Median Health Score =
MEDIANX ( VALUES ( DimPharmacy[PharmacyID] ), [Health Score] )

Returns maximum health score across all pharmacies.

Max Health Score = MAXX(VALUES(DimPharmacy[PharmacyID]), [Health Score])

Returns minimum health score across all pharmacies.

Min Health Score =
MINX ( VALUES ( DimPharmacy[PharmacyID] ), [Health Score] )

Calculates the spread between maximum and minimum health scores.

Health Score Range =
[Max Health Score] - [Min Health Score]

Counts pharmacies with health score of 90 or above.

Excellent Performers =
COUNTROWS (
    FILTER ( VALUES ( DimPharmacy[PharmacyID] ), [Health Score] >= 90 )
)

Counts pharmacies with health score between 75 and 90.

Good Performers =
COUNTROWS (
    FILTER (
        VALUES ( DimPharmacy[PharmacyID] ),
        [Health Score] >= 75 && [Health Score] < 90
    )
)

Sums count of Excellent and Good rated pharmacies.

Excellent and Good =
VAR Excel = [Excellent Performers]
VAR Good = [Good Performers]
RETURN
    Excel + Good

Calculates percentage of pharmacies rated Excellent or Good.

% Excellent or Good =
VAR TotalPharmacies =
    DISTINCTCOUNT ( DimPharmacy[PharmacyID] )
VAR GoodOrExcellent = [Excellent and Good]
RETURN
    DIVIDE ( GoodOrExcellent, TotalPharmacies )

Counts pharmacies with health score below 50 requiring critical attention.

Critical Locations =
CALCULATE (
    DISTINCTCOUNT ( DimPharmacy[PharmacyID] ),
    FILTER ( ALL ( DimPharmacy ), [Health Score] < 50 )
)

Returns text message indicating number of pharmacies with score <=75 needing intervention.

Pharmacies Requiring Intervention =
VAR Pharma =
    COUNTROWS (
        FILTER (
            ADDCOLUMNS ( VALUES ( DimPharmacy[PharmacyID] ), "Score", [Health Score] ),
            [Score] <= 75
        )
    )
VAR CountIntervention = Pharma
RETURN
    SWITCH (
        TRUE (),
        CountIntervention = 0, "No pharmacies require intervention.",
        CountIntervention = 1, "1 pharmacy requires intervention.",
        FORMAT ( CountIntervention, "0" ) & " pharmacies require intervention."
    )

Generates natural language summary of health performance.

Health Commentary =
VAR PercentGood = [% Excellent or Good]
VAR CriticalCount = [Critical Locations]
VAR CriticalText =
    SWITCH (
        TRUE (),
        CriticalCount = 0, "No locations require immediate intervention.",
        CriticalCount = 1, "1 location flagged for immediate intervention.",
        CriticalCount > 1, FORMAT ( CriticalCount, "0" ) & " locations flagged for immediate intervention."
    )
RETURN
    FORMAT ( PercentGood, "0%" ) & " of pharmacies achieved a Good or Excellent rating. " & CriticalText

Filter measure to show only pharmacies matching selected health status group.

Filter by Status =
VAR CurrentScore = [Health Score]
RETURN
    IF (
        COUNTROWS (
            FILTER (
                StatusGroups,
                CurrentScore >= StatusGroups[Min Score]
                    && CurrentScore <= StatusGroups[Max Score]
            )
        ) > 0,
        1,
        BLANK ()
    )

Counts distinct sales orders (each SalesID represents one order).

Total Sales Order =
DISTINCTCOUNT ( FactSales[SalesID] )

Returns order count for the same period in previous year.

Previous Year Sales Order =
IF (
    HASONEVALUE ( DimDate[Year] ),
    CALCULATE ( [Total Sales Order], DATEADD ( DimDate[Date], -1, YEAR ) )
)

Returns absolute change in order count from previous month with +/- symbol.

Order MoM Value =
VAR HasOneMonth =
    HASONEVALUE ( 'DimDate'[Year Month] )
VAR CurrentOrders = [Total Sales Order]
VAR PreviousOrders =
    CALCULATE ( [Total Sales Order], DATEADD ( 'DimDate'[Date], -1, MONTH ) )
VAR MoMChange = CurrentOrders - PreviousOrders
RETURN
    IF (
        NOT HasOneMonth || ISBLANK ( PreviousOrders ),
        "--",
        IF (
            MoMChange > 0,
            "+" & FORMAT ( MoMChange, "#,##0" ),
            "-" & FORMAT ( ABS ( MoMChange ), "#,##0" )
        )
    )

Returns formatted percentage change with up/down arrows and previous month label.

Order MoM % =
VAR HasOneMonth =
    HASONEVALUE ( 'DimDate'[Year Month] )
VAR CurrentOrders = [Total Sales Order]
VAR PreviousOrders =
    CALCULATE ( [Total Sales Order], DATEADD ( 'DimDate'[Date], -1, MONTH ) )
VAR MoMChange = CurrentOrders - PreviousOrders
VAR MoMPercent =
    DIVIDE ( MoMChange, PreviousOrders )
VAR PrevMonthLabel =
    FORMAT (
        CALCULATE ( MAX ( 'DimDate'[Date] ), DATEADD ( 'DimDate'[Date], -1, MONTH ) ),
        "MMM-yyyy"
    )
RETURN
    IF (
        NOT HasOneMonth || ISBLANK ( PreviousOrders ),
        "--",
        IF (
            MoMChange > 0,
            "▲ " & FORMAT ( MoMPercent, "0.0%" ) & " vs " & PrevMonthLabel,
            "▼ " & FORMAT ( ABS ( MoMPercent ), "0.0%" ) & " vs " & PrevMonthLabel
        )
    )

Returns raw decimal value of month-over-month order percentage change.

Order MoM % 2 =
VAR HasOneMonth =
    HASONEVALUE ( 'DimDate'[Year Month] )
VAR CurrentOrders = [Total Sales Order]
VAR PreviousOrders =
    CALCULATE ( [Total Sales Order], DATEADD ( 'DimDate'[Date], -1, MONTH ) )
VAR MoMChange = CurrentOrders - PreviousOrders
VAR MoMPercent =
    DIVIDE ( MoMChange, PreviousOrders )
RETURN
    IF ( NOT HasOneMonth || ISBLANK ( PreviousOrders ), "--", MoMPercent )

Returns color code: blue for order increase/stable, red for order decrease.

Order MoM Color =
VAR PreviousOrder =
    CALCULATE ( [Total Sales Order], DATEADD ( 'DimDate'[Date], -1, MONTH ) )
VAR Change = [Total Sales Order] - PreviousOrder
RETURN
    IF (
        ISBLANK ( PreviousOrder ),
        BLANK (),
        IF ( Change >= 0, "#3D90D7", "#DC2626" )
    )

Returns background color: light blue for order increase, light red for order decrease.

Order MoM Bg Color =
VAR HasOneMonth =
    HASONEVALUE ( 'DimDate'[Year Month] )
VAR CurrentOrders = [Total Sales Order]
VAR PreviousOrders =
    CALCULATE ( [Total Sales Order], DATEADD ( 'DimDate'[Date], -1, MONTH ) )
VAR MoMChange = CurrentOrders - PreviousOrders
RETURN
    IF (
        NOT HasOneMonth || ISBLANK ( PreviousOrders ),
        "",
        IF ( MoMChange > 0, "#AFDDFF", "#F7CACA" )
    )

Calculates total profit by summing MarginEUR from fact table.

Total Profit =
SUM ( FactSales[MarginEUR] )

Calculates profit margin percentage as profit divided by sales.

Profit Margin =
DIVIDE ( [Total Profit], [Total Sales], 0 )

Dynamically calculates profit target based on pharmacy segment and month.

Profit Target =
VAR PharmacyMonthCombinations =
    CROSSJOIN (
        VALUES ( DimPharmacy[PharmacyID] ),
        DISTINCT (
            SELECTCOLUMNS (
                VALUES ( DimDate[Date] ),
                "MonthEnd", EOMONTH ( DimDate[Date], 0 )
            )
        )
    )
RETURN
    SUMX (
        PharmacyMonthCombinations,
        VAR CurrentPharmacyID = [PharmacyID]
        VAR CurrentMonthEnd = [MonthEnd]
        VAR CurrentSegment =
            CALCULATE (
                MAX ( DimPharmacy[PharmacySegment] ),
                ALL ( DimPharmacy ),
                DimPharmacy[PharmacyID] = CurrentPharmacyID
            )
        VAR Target =
            CALCULATE (
                MAX ( 'Targets Table'[Profit Target Per Pharmacy] ),
                ALL ( 'Targets Table' ),
                'Targets Table'[PharmacySegment] = CurrentSegment,
                'Targets Table'[Target Date] = CurrentMonthEnd
            )
        RETURN
            IF ( ISBLANK ( Target ), 0, Target )
    )

Formats profit and target for display with line break.

ProfitTargetDisplay =
"Profit: " & FORMAT ( [Total Profit], "#,##0" ) & UNICHAR ( 10 ) & "Target: 🎯 "
    & FORMAT ( [Profit Target], "#,##0" )

Calculates variance between actual profit and profit target.

V-Profit =
[Total Profit] - [Profit Target]

Returns red color if profit below target, blue if above or meeting target.

Pv Color =
IF ( [V-Profit] < 0, "Red", "#3D90D7" )

Calculates profit achievement percentage capped at 100% (1).

Profit Achievement % =
MIN ( DIVIDE ( [Total Profit], [Profit Target], 0 ), 1 )

Counts pharmacies where actual profit meets or exceeds profit target.

Pharmacies Meeting Profit Target =
COUNTROWS (
    FILTER ( VALUES ( DimPharmacy[PharmacyID] ), [Total Profit] >= [Profit Target] )
)

Calculates percentage of pharmacies meeting profit target.

Pharmacy Meeting Profit Target % =
DIVIDE ( [Pharmacies Meeting Profit Target], [Total Pharmacies], 0 )

Helper text for displaying pharmacy target achievement.

Label2 =
VAR Pharm = [Total Pharmacies]
VAR PharmT = [Pharmacies Meeting Profit Target]
RETURN
    "Out of" & " " & Pharm & " " & "Pharmacys"

Returns total profit for the same period in previous year.

Previous Year Profit =
IF (
    HASONEVALUE ( DimDate[Year] ),
    CALCULATE ( [Total Profit], DATEADD ( DimDate[Date], -1, YEAR ) )
)

Returns absolute change in profit from previous month with +/- symbol.

Profit MoM Value =
VAR HasOneMonth =
    HASONEVALUE ( 'DimDate'[Year Month] )
VAR CurrentProfit = [Total Profit]
VAR PreviousProfit =
    CALCULATE ( [Total Profit], DATEADD ( 'DimDate'[Date], -1, MONTH ) )
VAR MoMChange = CurrentProfit - PreviousProfit
RETURN
    IF (
        NOT HasOneMonth || ISBLANK ( PreviousProfit ),
        "--",
        IF (
            MoMChange > 0,
            "+" & FORMAT ( MoMChange, "#,##0" ),
            "-" & FORMAT ( ABS ( MoMChange ), "#,##0" )
        )
    )

Returns formatted percentage change with up/down arrows and previous month label.

Profit MoM % =
VAR HasOneMonth =
    HASONEVALUE ( 'DimDate'[Year Month] )
VAR CurrentProfit = [Total Profit]
VAR PreviousProfit =
    CALCULATE ( [Total Profit], DATEADD ( 'DimDate'[Date], -1, MONTH ) )
VAR MoMChange = CurrentProfit - PreviousProfit
VAR MoMPercent =
    DIVIDE ( MoMChange, PreviousProfit )
VAR PrevMonthLabel =
    FORMAT (
        CALCULATE ( MAX ( 'DimDate'[Date] ), DATEADD ( 'DimDate'[Date], -1, MONTH ) ),
        "MMM-yyyy"
    )
RETURN
    IF (
        NOT HasOneMonth || ISBLANK ( PreviousProfit ),
        "--",
        IF (
            MoMChange > 0,
            "▲ " & FORMAT ( MoMPercent, "0.0%" ) & " vs " & PrevMonthLabel,
            "▼ " & FORMAT ( ABS ( MoMPercent ), "0.0%" ) & " vs " & PrevMonthLabel
        )
    )

Returns raw decimal value of month-over-month profit percentage change.

Profit MoM % 2 =
VAR HasOneMonth =
    HASONEVALUE ( 'DimDate'[Year Month] )
VAR CurrentProfit = [Total Profit]
VAR PreviousProfit =
    CALCULATE ( [Total Profit], DATEADD ( 'DimDate'[Date], -1, MONTH ) )
VAR MoMChange = CurrentProfit - PreviousProfit
VAR MoMPercent =
    DIVIDE ( MoMChange, PreviousProfit )
RETURN
    IF ( NOT HasOneMonth || ISBLANK ( PreviousProfit ), "--", MoMPercent )

Returns color code: blue for profit increase/stable, red for profit decrease.

Profit MoM Color =
VAR PreviousProfit =
    CALCULATE ( [Total Profit], DATEADD ( 'DimDate'[Date], -1, MONTH ) )
VAR Change = [Total Profit] - PreviousProfit
RETURN
    IF (
        ISBLANK ( PreviousProfit ),
        BLANK (),
        IF ( Change >= 0, "#3D90D7", "#DC2626" )
    )

Returns background color: light blue for profit increase, light red for profit decrease.

Profit MoM Bg Color =
VAR HasOneMonth =
    HASONEVALUE ( 'DimDate'[Year Month] )
VAR CurrentProfit = [Total Profit]
VAR PreviousProfit =
    CALCULATE ( [Total Profit], DATEADD ( 'DimDate'[Date], -1, MONTH ) )
VAR MoMChange = CurrentProfit - PreviousProfit
RETURN
    IF (
        NOT HasOneMonth || ISBLANK ( PreviousProfit ),
        "",
        IF ( MoMChange > 0, "#AFDDFF", "#F7CACA" )
    )

Returns profit margin from previous month.

Margin % PM =
CALCULATE ( [Profit Margin], DATEADD ( 'DimDate'[Date], -1, MONTH ) )

Calculates the change in profit margin percentage from previous month.

Margin Gap =
[Profit MoM % 2] - [Margin % PM]

Calculates total sales revenue by summing RevenueEUR from fact table.

Total Sales = SUM(FactSales[RevenueEUR])

Calculates sales achievement percentage capped at 100% (1).

Sales Achievement =
MIN ( DIVIDE ( [Total Sales], [Sales Target], 0 ), 1 )

Dynamically calculates sales target based on pharmacy segment and month.

Sales Target =
VAR PharmacyMonthCombinations =
    CROSSJOIN (
        VALUES ( DimPharmacy[PharmacyID] ),
        DISTINCT (
            SELECTCOLUMNS (
                VALUES ( DimDate[Date] ),
                "MonthEnd", EOMONTH ( DimDate[Date], 0 )
            )
        )
    )
RETURN
    SUMX (
        PharmacyMonthCombinations,
        VAR CurrentPharmacyID = [PharmacyID]
        VAR CurrentMonthEnd = [MonthEnd]
        VAR CurrentSegment =
            CALCULATE (
                MAX ( DimPharmacy[PharmacySegment] ),
                ALL ( DimPharmacy ),
                DimPharmacy[PharmacyID] = CurrentPharmacyID
            )
        VAR Target =
            CALCULATE (
                MAX ( 'Targets Table'[Sales Target Per Pharmacy] ),
                ALL ( 'Targets Table' ),
                'Targets Table'[PharmacySegment] = CurrentSegment,
                'Targets Table'[Target Date] = CurrentMonthEnd
            )
        RETURN
            IF ( ISBLANK ( Target ), 0, Target )
    )

Formats sales and target for display with line break.

SalesTargetDisplay =
"Sales: " & FORMAT ( [Total Sales], "#,##0" ) & UNICHAR ( 10 ) & "Target: 🎯 "
    & FORMAT ( [Sales Target], "#,##0" )

Calculates variance between actual sales and sales target.

Variance = [Total Sales] - [Sales Target]

Returns red color if sales below target, blue if above or meeting target.

V color = IF([Variance] < 0, "Red", "#3D90D7")

Calculates sales achievement percentage capped at 100% (same as Sales Achievement).

Sales Achievement % =
MIN ( DIVIDE ( [Total Sales], [Sales Target], 0 ), 1 )

Counts pharmacies where actual sales meet or exceed sales target.

Pharmacies Meeting Sales Target =
COUNTROWS (
    FILTER ( VALUES ( DimPharmacy[PharmacyID] ), [Total Sales] >= [Sales Target] )
)

Calculates percentage of pharmacies meeting sales target.

Pharmacy Meeting Target % =
DIVIDE ( [Pharmacies Meeting Sales Target], [Total Pharmacies], 0 )

Helper text for displaying pharmacy target achievement.

Label =
VAR Pharm = [Total Pharmacies]
VAR PharmT = [Pharmacies Meeting Sales Target]
RETURN
    "Out of" & " " & Pharm & " " & "Pharmacys"

Returns total sales for the same period in previous year.

Previous Year Sales =
IF (
    HASONEVALUE ( DimDate[Year] ),
    CALCULATE ( [Total Sales], DATEADD ( DimDate[Date], -1, YEAR ) )
)

Returns absolute change in sales from previous month with +/- symbol.

Sales MoM Value =
VAR HasOneMonth =
    HASONEVALUE ( 'DimDate'[Year Month] )
VAR CurrentRevenue = [Total Sales]
VAR PreviousRevenue =
    CALCULATE ( [Total Sales], DATEADD ( 'DimDate'[Date], -1, MONTH ) )
VAR MoMChange = CurrentRevenue - PreviousRevenue
RETURN
    IF (
        NOT HasOneMonth || ISBLANK ( PreviousRevenue ),
        "--",
        IF (
            MoMChange > 0,
            "+" & FORMAT ( MoMChange, "#,##0" ),
            "-" & FORMAT ( ABS ( MoMChange ), "#,##0" )
        )
    )

Returns formatted percentage change with up/down arrows and previous month label.

Revenue MoM % =
VAR HasOneMonth =
    HASONEVALUE ( 'DimDate'[Year Month] )
VAR CurrentRevenue = [Total Sales]
VAR PreviousRevenue =
    CALCULATE ( [Total Sales], DATEADD ( 'DimDate'[Date], -1, MONTH ) )
VAR MoMChange = CurrentRevenue - PreviousRevenue
VAR MoMPercent =
    DIVIDE ( MoMChange, PreviousRevenue )
VAR PrevMonthLabel =
    FORMAT (
        CALCULATE ( MAX ( 'DimDate'[Date] ), DATEADD ( 'DimDate'[Date], -1, MONTH ) ),
        "MMM-yyyy"
    )
RETURN
    IF (
        NOT HasOneMonth || ISBLANK ( PreviousRevenue ),
        "--",
        IF (
            MoMChange > 0,
            "▲ " & FORMAT ( MoMPercent, "0.0%" ) & " vs " & PrevMonthLabel,
            "▼ " & FORMAT ( ABS ( MoMPercent ), "0.0%" ) & " vs " & PrevMonthLabel
        )
    )

Returns color code: blue for sales increase/stable, red for sales decrease.

Sales MoM Color =
VAR PreviousRevenue =
    CALCULATE ( [Total Sales], DATEADD ( 'DimDate'[Date], -1, MONTH ) )
VAR Change = [Total Sales] - PreviousRevenue
RETURN
    IF (
        ISBLANK ( PreviousRevenue ),
        BLANK (),
        IF ( Change >= 0, "#3D90D7", "#DC2626" )
    )

Returns background color: light blue for sales increase, light red for sales decrease.

Sales MoM Bg Color =
VAR HasOneMonth =
    HASONEVALUE ( 'DimDate'[Year Month] )
VAR CurrentRevenue = [Total Sales]
VAR PreviousRevenue =
    CALCULATE ( [Total Sales], DATEADD ( 'DimDate'[Date], -1, MONTH ) )
VAR MoMChange = CurrentRevenue - PreviousRevenue
RETURN
    IF (
        NOT HasOneMonth || ISBLANK ( PreviousRevenue ),
        "",
        IF ( MoMChange > 0, "#AFDDFF", "#F7CACA" )
    )

Calculates total sales while preserving dosage form filter but removing other product filters.

Sales by Dosage Form =
CALCULATE ( [Total Sales], ALLEXCEPT ( DimProduct, DimProduct[Dosage Form] ) )

Calculates total units sold by summing UnitsSold from fact table.

Total Units = SUM(FactSales[UnitsSold])

Returns total units for the same period in previous year (note: named Previous Month but calculates previous year).

Previous Month Units =
IF (
    HASONEVALUE ( DimDate[Year] ),
    CALCULATE ( [Total Units], DATEADD ( DimDate[Date], -1, YEAR ) )
)

Returns absolute change in units from previous month with +/- symbol.

Units MoM Value =
VAR HasOneMonth =
    HASONEVALUE ( 'DimDate'[Year Month] )
VAR CurrentUnits = [Total Units]
VAR PreviousUnits =
    CALCULATE ( [Total Units], DATEADD ( 'DimDate'[Date], -1, MONTH ) )
VAR MoMChange = CurrentUnits - PreviousUnits
RETURN
    IF (
        NOT HasOneMonth || ISBLANK ( PreviousUnits ),
        "--",
        IF (
            MoMChange > 0,
            "+" & FORMAT ( MoMChange, "#,##0" ),
            "-" & FORMAT ( ABS ( MoMChange ), "#,##0" )
        )
    )

Returns formatted percentage change with up/down arrows and previous month label.

Units MoM % =
VAR HasOneMonth =
    HASONEVALUE ( 'DimDate'[Year Month] )
VAR CurrentUnits = [Total Units]
VAR PreviousUnits =
    CALCULATE ( [Total Units], DATEADD ( 'DimDate'[Date], -1, MONTH ) )
VAR MoMChange = CurrentUnits - PreviousUnits
VAR MoMPercent =
    DIVIDE ( MoMChange, PreviousUnits )
VAR PrevMonthLabel =
    FORMAT (
        CALCULATE ( MAX ( 'DimDate'[Date] ), DATEADD ( 'DimDate'[Date], -1, MONTH ) ),
        "MMM-yyyy"
    )
RETURN
    IF (
        NOT HasOneMonth || ISBLANK ( PreviousUnits ),
        "--",
        IF (
            MoMChange > 0,
            "▲ " & FORMAT ( MoMPercent, "0.0%" ) & " vs " & PrevMonthLabel,
            "▼ " & FORMAT ( ABS ( MoMPercent ), "0.0%" ) & " vs " & PrevMonthLabel
        )
    )

Returns raw decimal value of month-over-month units percentage change.

Units MoM % 2 =
VAR HasOneMonth =
    HASONEVALUE ( 'DimDate'[Year Month] )
VAR CurrentUnits = [Total Units]
VAR PreviousUnits =
    CALCULATE ( [Total Units], DATEADD ( 'DimDate'[Date], -1, MONTH ) )
VAR MoMChange = CurrentUnits - PreviousUnits
VAR MoMPercent =
    DIVIDE ( MoMChange, PreviousUnits )
RETURN
    IF ( NOT HasOneMonth || ISBLANK ( PreviousUnits ), "--", MoMPercent )

Returns color code: blue for units increase/stable, red for units decrease.

Units MoM Color =
VAR PreviousUnits =
    CALCULATE ( [Total Units], DATEADD ( 'DimDate'[Date], -1, MONTH ) )
VAR Change = [Total Units] - PreviousUnits
RETURN
    IF (
        ISBLANK ( PreviousUnits ),
        BLANK (),
        IF ( Change >= 0, "#3D90D7", "#DC2626" )
    )

Returns background color: light blue for units increase, light red for units decrease.

Units MoM Bg Color =
VAR HasOneMonth =
    HASONEVALUE ( 'DimDate'[Year Month] )
VAR CurrentUnits = [Total Units]
VAR PreviousUnits =
    CALCULATE ( [Total Units], DATEADD ( 'DimDate'[Date], -1, MONTH ) )
VAR MoMChange = CurrentUnits - PreviousUnits
RETURN
    IF (
        NOT HasOneMonth || ISBLANK ( PreviousUnits ),
        "",
        IF ( MoMChange > 0, "#AFDDFF", "#F7CACA" )
    )

Calculates total sales revenue where promotion flag is Yes.

Promo Sales =
CALCULATE ( [Total Sales], FactSales[PromoFlag] = "Yes" )

Returns promo sales for the same period in previous year.

Previous Year Promo Sales =
IF (
    HASONEVALUE ( DimDate[Year] ),
    CALCULATE ( [Promo Sales], DATEADD ( DimDate[Date], -1, YEAR ) )
)

Returns absolute change in promo sales from previous month with +/- symbol.

Promo MoM Value =
VAR HasOneMonth =
    HASONEVALUE ( 'DimDate'[Year Month] )
VAR CurrentPromo = [Promo Sales]
VAR PreviousPromo =
    CALCULATE ( [Promo Sales], DATEADD ( 'DimDate'[Date], -1, MONTH ) )
VAR MoMChange = CurrentPromo - PreviousPromo
RETURN
    IF (
        NOT HasOneMonth || ISBLANK ( PreviousPromo ),
        "--",
        IF (
            MoMChange > 0,
            "+" & FORMAT ( MoMChange, "#,##0" ),
            "-" & FORMAT ( ABS ( MoMChange ), "#,##0" )
        )
    )

Returns formatted percentage change with up/down arrows and previous month label.

Promo Sales MoM % =
VAR HasOneMonth =
    HASONEVALUE ( 'DimDate'[Year Month] )
VAR CurrentPromo = [Promo Sales]
VAR PreviousPromo =
    CALCULATE ( [Promo Sales], DATEADD ( 'DimDate'[Date], -1, MONTH ) )
VAR MoMChange = CurrentPromo - PreviousPromo
VAR MoMPercent =
    DIVIDE ( MoMChange, PreviousPromo )
VAR PrevMonthLabel =
    FORMAT (
        CALCULATE ( MAX ( 'DimDate'[Date] ), DATEADD ( 'DimDate'[Date], -1, MONTH ) ),
        "MMM-yyyy"
    )
RETURN
    IF (
        NOT HasOneMonth || ISBLANK ( PreviousPromo ),
        "--",
        IF (
            MoMChange > 0,
            "▲ " & FORMAT ( MoMPercent, "0.0%" ) & " vs " & PrevMonthLabel,
            "▼ " & FORMAT ( ABS ( MoMPercent ), "0.0%" ) & " vs " & PrevMonthLabel
        )
    )

Returns color code: blue for promo sales increase/stable, red for promo sales decrease.

Promo MoM Color =
VAR PreviousPromo =
    CALCULATE ( [Promo Sales], DATEADD ( 'DimDate'[Date], -1, MONTH ) )
VAR Change = [Promo Sales] - PreviousPromo
RETURN
    IF (
        ISBLANK ( PreviousPromo ),
        BLANK (),
        IF ( Change >= 0, "#3D90D7", "#DC2626" )
    )

Returns background color: light blue for promo sales increase, light red for promo sales decrease.

Promo MoM Bg Color =
VAR HasOneMonth =
    HASONEVALUE ( 'DimDate'[Year Month] )
VAR CurrentPromo = [Promo Sales]
VAR PreviousPromo =
    CALCULATE ( [Promo Sales], DATEADD ( 'DimDate'[Date], -1, MONTH ) )
VAR MoMChange = CurrentPromo - PreviousPromo
RETURN
    IF (
        NOT HasOneMonth || ISBLANK ( PreviousPromo ),
        "",
        IF ( MoMChange > 0, "#AFDDFF", "#F7CACA" )
    )

Counts total number of distinct pharmacies.

Total Pharmacies =
DISTINCTCOUNT ( DimPharmacy[PharmacyID] )

Returns text string indicating number of countries pharmacies operate in.

Countries =
VAR Country =
    DISTINCTCOUNT ( DimPharmacy[Country] )
RETURN
    "Across" & " " & Country & " " & "Countries"

Counts pharmacies meeting both profit target AND sales target simultaneously.

Pharmacies Meeting Both Targets =
COUNTROWS (
    FILTER (
        VALUES ( DimPharmacy[PharmacyID] ),
        [Total Profit] >= [Profit Target] && [Total Sales] >= [Sales Target]
    )
)

Ranks pharmacies by health score in descending order with dense ranking.

Rank Pharm =
RANKX ( ALL ( DimPharmacy[PharmacyName] ), [Health Score],, DESC, DENSE )

Generates natural language commentary comparing profit, cost, and margin performance month-over-month.

Dynamic Performance Commentary =
IF (
    NOT HASONEVALUE ( 'DimDate'[Year Month] ),
    "Select a single month to view performance insight.",
    VAR ProfitMoM = [Profit MoM % 2]
    VAR CostMoM = [Cost MoM % 2]
    VAR MarginGap = [Margin Gap]
    VAR PMName = 'KPI Table'[KPI Previous Month Name]
    VAR ProfitDirection =
        SWITCH (
            TRUE (),
            ProfitMoM > 0, "increased",
            ProfitMoM < 0, "declined",
            "remained flat"
        )
    VAR CostDirection =
        SWITCH (
            TRUE (),
            CostMoM > 0, "increased",
            CostMoM < 0, "declined",
            "remained flat"
        )
    VAR MarginDirection =
        SWITCH (
            TRUE (),
            MarginGap > 0, "expanded",
            MarginGap < 0, "contracted",
            "remained stable"
        )
    VAR EfficiencySignal =
        SWITCH (
            TRUE (),
            ProfitMoM > CostMoM, "improved cost efficiency",
            ProfitMoM < CostMoM, "pressure on margins",
            "stable cost dynamics"
        )
    RETURN
        "Profit " & ProfitDirection & " " & FORMAT ( ABS ( ProfitMoM ), "0.0%" ) & " MoM while cost " & CostDirection & " "
            & FORMAT ( ABS ( CostMoM ), "0.0%" ) & ", resulting in a "
            & FORMAT ( ABS ( MarginGap ), "0.0" ) & "pp margin " & MarginDirection & ". This signals " & EfficiencySignal & " " & "vs" & " " & PMName & "."
)

Why This Dashboard Improves Decision-Making

This dashboard enables:

Faster executive reporting
Better inventory planning
Margin optimization
Data-driven promotional decisions
Store benchmarking
Revenue growth
Cost reduction

This is not just a report.

It is a complete pharmacy decision intelligence system.

Skip Months of Development

Access a ready-to-use Power BI template designed specifically for pharmacy analytics and start making data-driven decisions immediately.

Conclusion

Pharmacy leaders cannot rely on delayed spreadsheets to manage performance in a competitive environment.

A centralized dashboard provides clarity across:

Every branch
Every product
Every KPI

It answers critical questions like:

Which pharmacy is underperforming?
Which products drive the highest profit?
Are promotions improving margins?
Where is immediate action required?

This solution is built to deliver those answers instantly.

Take the Next Step

Explore the full dashboard experience and access the complete Pharmacy Power BI template designed for smarter, faster healthcare decision-making.

View all report pages
Understand KPI logic
Analyze real dashboard behavior
Access the complete solution

Frequently Asked Questions (FAQ)

Is this dashboard customizable?

Yes, the Power BI template can be customized for your pharmacy structure, KPIs, and reporting needs.

What type of data is required to implement this dashboard?

The dashboard typically requires transactional sales data, product details, pharmacy/store information, and target metrics. It is designed to work with structured datasets commonly available in pharmacy management systems.

How frequently should this dashboard be updated for accurate insights?

For best results, the dashboard should be connected to a daily or near real-time data refresh. This ensures that decisions are always based on the latest sales, cost, and inventory trends.

Can this dashboard help identify loss-making products or categories?

Yes, it provides detailed profitability analysis at product and category levels, helping identify low-margin or loss-making items so corrective actions can be taken.

Is this dashboard useful for small pharmacy chains or only large enterprises?

The structure is scalable, making it suitable for both small pharmacy groups and large multi-location healthcare businesses.

About the Author

Mandavi Yadav

Blending technical expertise with design thinking to build efficient Power BI dashboards. Through my blogs, I aim to guide users in making the most of data visualization tools.