Uncovering Pharmaceutical Sales Trends: A Power BI Analysis of Drug Demand in Serbia

6 min readNov 6, 2024

Introduction

In a data-driven exploration of pharmaceutical sales, I used Power BI to analyze demand fluctuations for various drug classes in a pharmacy in Niš, Serbia. This project, spanning six years (2014–2019), provided insights into drug consumption patterns, seasonal demands, and the impact of weekly trends. Here’s a deep dive into the methods, insights, and visual storytelling that brought this data to life.

Project Overview

The goal of this analysis was to uncover patterns in the sales of pharmaceuticals, categorized under the Anatomical Therapeutic Chemical (ATC) classification system. This system groups drugs based on their therapeutic use, providing clarity on the types of medications analyzed and the overall trends across six years.

Data Source:https://www.kaggle.com/datasets/milanzdravkovic/pharma-sales-data/data

License: https://creativecommons.org/licenses/by-nc/4.0/

Original Authors: Zdravković, M., Đorđević, J., Catić-Đorđević, A., Pavlović, S., Ivković, M. Case study: univariate time series analysis and forecasting of pharmaceutical products’ sales data at small scale. In: Zdravković, M., Konjović, Z., Trajanović, M. (Eds.) ICIST 2020 Proceedings, pp.1–4, 2020

Understanding the Data

The dataset comprised:

Hourly Sales Data: Sales volumes measured in grams, captured hourly, across drug categories.
ATC Drug Class Info: Supplementary data on each drug category’s purpose, description, and cumulative sales volumes in grams.

Table of drug classes and their descriptions

This combination of time-stamped sales data and drug classification enabled a detailed exploration of trends based on usage, demand, and seasonality.

Dataset Columns Explained

The dataset consists of two main tables with specific columns that each serve a unique role in the analysis:

Sales Data Table

This table captures hourly sales records for each drug class, allowing for an in-depth analysis of sales trends over time. The table contained 9 columns and over 50,000 records. Key columns include:

datum: This column holds the timestamp (date and hour) for each record. After standardizing it to a datetime format, this field became essential for grouping and analyzing sales by different time intervals, such as hour, day, and month.
Drug Class Columns (e.g., M01AB, M01AE, N02BA, etc.): Each column represents sales volume in grams for a specific drug class, identified by its ATC code.

ATC Drug Information Table

This reference table provides essential background information on each drug class, enhancing the interpretability of sales data:

ATC Code: Unique identifier for each drug category, following the Anatomical Therapeutic Chemical (ATC) classification system.
Class Name: Descriptive name for each drug class, such as “Propionic acid derivatives” or “Anxiolytics,” which categorizes the drugs by their primary therapeutic use.
Description: Provides additional context on each drug class, describing the general purpose and use of the drugs within it (e.g., pain relief, anti-inflammatory, or fever reduction).

Data Cleaning and Preparation

For this project, I implemented various cleaning and transformation steps to prepare the data for analysis in Power BI. Here’s a step-by-step overview:

Data Cleaning:

Date Formatting: The datum column in the sales data, containing timestamps, was standardized to a consistent datetime format.
Handling Missing Values: Missing values in the sales data were replaced with 0, assuming that no sales occurred during those periods.

2. Data Transformation (Unpivoting Drug Classes):

To streamline the analysis, I unpivoted the drug class columns (e.g., M01AB, N02BE) into two columns: Drug Class and Sales Volume. This transformation placed each drug class in a single column with its corresponding sales volume, reducing the overall column count and making it easier to filter, group, and compare across drug classes.

3. Data Model Structure:

I used Power BI’s Data Model to connect the sales data with the ATC Drug Information table.
Additionally, I added a Calendar Table to the data model using DAX. This table is used for supporting detailed time-based filtering and grouping. The Calendar Table was linked to the sales data to help capture trends across weeks, months, and seasons.

Key Findings and Visual Storytelling with Power BI

1. Weekly Demand Fluctuations

Weekends were peak times for sales in most drug categories, while Thursdays had the lowest average sales volume.
N02BE, M01AB, and M01AE (primarily pain relief and fever reduction drugs) showed higher sales on weekends. This trend aligns with common healthcare needs over weekends, where consumers might stock up or manage recurring conditions.
In contrast, N05B (drugs to reduce anxiety) had the lowest weekend sales, possibly reflecting work-week stress patterns, with anxiety-reducing medications needed more during weekdays.

2. Seasonality Trends

R06 (Antihistamines) sales spiked during the summer months, correlating with seasonal allergies.
M01AE and M01AB showed a clear drop in sales toward the end of the year, probably reflecting a decreased need for pain and fever management drugs in colder months.
R03 (Drugs for asthma and COPD), on the other hand, saw higher sales in winter, likely due to the increase in respiratory issues during colder weather.

3. Category-Specific Peaks

N02B3 (Pain relief and fever reduction) stood out as the most frequently sold category, underlining the pharmacy’s need to ensure these medications are always in stock.
N05C (Sedatives) had fluctuating sales throughout our analysis and was the least sold Drug Class.

Interactive Dashboard and Design Choices

I designed the Power BI dashboard to provide users with an interactive experience. The home page, shown above, acts as a navigational hub, leading to:

Sales Overview Page: This page breaks down hourly and weekly sales trends in more detail, allowing viewers to filter by specific drug classes.
Calendar View Page: This visualization enabled a closer look at monthly and yearly trends, offering insights into how sales fluctuate across broader timeframes.
To explore this dashboard and experience its full capacity, CLICK HERE!

Conclusion

This Power BI analysis helped uncover actionable insights into the pharmacy’s drug sales. From weekly demand fluctuations to seasonal spikes, each finding helps optimize inventory strategies and better understand customer needs. Power BI’s capabilities in data visualization and interactivity made it possible to communicate these insights effectively, turning raw data into a strategic asset.

Whether you’re in the pharmaceutical industry or managing retail data, Power BI offers the tools to make informed decisions. This project demonstrated how data storytelling and powerful visuals can provide clarity, drive action, and offer a roadmap for future planning.