EN | PL | DE | SE

ABC analysis and methods for its dynamic representation in Microsoft Power BI

by | Dec 10, 2024 | Blog

I. Introduction

ABC grading is a technique used to analyse the value of sales revenue or sales volume based on the Pareto principle, which states that 20% of products generate 80% of revenue, and the key to correct stock management and taking care of these most profitable products is to find them, classify them into a specific category and obtain material for further analysis.

There are three basic categories (grades) of assessments:

Grade A

These are the best-selling products; importantly, it is not just the value of sales that is analysed, but more the volume of sales. The best-selling products are those that meet all parameters. The business should focus mainly on them, as they generate 80% of the profit through numerous promotional campaigns, personalised service through dedicated channels, or even priority in loading, unloading of goods used to produce these best-selling products.

Grade B

These are medium-selling products, usually accounting for around 15% of the company’s profit and making up around 30% of the product range. Their grade consists of fulfilling strict minimums, but due to less frequent promotions and seasonality of sales, they do not constitute grade A.

Grade C

These are the worst-selling products in terms of value, even if their total unit sales are high, the profit figures achieved are too low to pay attention to them. C-rated products are also often removed from the range to make room for products that can make a profit.

The minima that determine whether a product is more in the B or C grade are primarily:

      • Production minimum

        • This is the minimum amount of production that makes a profit.

      • Logistics minimum

        • This is the minimum order size whose transport cost is less than the profit the sale will bring.

      • Minimum market value

        • This is the minimum order size whose order will not cause production or logistical problems.

II. Tools for assessing the profitability of ABC products

Basic tools for assessing product profitability include:

MS Excel

The plethora of functions, transformations and visualisation capabilities make it the tool of choice.

Python

Fastest data analysis using Pandas, Numpy, Matplotlib/Plotly or Scipy libraries.

MS Power BI

Sales analysis and visualisation being a combination of transformations or dynamic transformations in the tool.

III. MS Power BI as the tool of the future for assessing ABC

MS Power BI is currently the tool of the future, most often chosen for ABC analysis because of the initial effort to build an automated report and then automatically redistribute it, refresh it and pay more attention to data security. MS Power BI is superior to MS Excel because it’s the next step in advanced analysis and sharing reports up to C-level and requires manual work by a team of analysts to work on it, create the analysis, charts and send them to a specific group of users. These are the functions that MS Power BI is currently able to perform for analytical departments at any time.

IV. Example of a simple ABC analysis

To calculate which products constitute a group of top sellers you need to:

      1. Either download sales data from a transaction system or build a process to download such data and store it in a specific location.
      2. Collate sales data to calculate the cumulative share of sales volume based on sorted sales (from largest to smallest).
      3. Break down products based on: 0-70% of total sales by value grade A, 70-95% of total is grade B and 5% of sales is grade C.

V. Description of the dataset used in the article

The dataset used in the example is an inventory of the product categories and subcategories of the supermarket’s business, with quantities of goods sold in each category. The dataset was generated with a Python script, so it has the characteristics of a normal distribution.

VI. ABC classification approach in practice

ABC analysis is currently done in 2 ways and both are correct:

The first

is to calculate the classification based on specific products completely at the data warehouse level. If a supermarket has 30,000 items and wants to perform ABC analysis for them, such a range of data will completely slow down the data model and dynamic calculation in Power BI will be either very long or even impossible.

The second

is to calculate dynamically on aggregated values. Calculating this in Power BI then enables a comprehensive and quick assessment of which aggregated subcategory has A-class characteristics and which needs to be focused on in the following months. In this article, dynamic segmentation is presented based on 2 methodologies: either an approach to analysis through measures or an approach to analysis through dynamically calculated structures.

VII. First method of dynamic classification ABC

Method one assumes that the user does not have the ability to edit the semantic data model by adding more database objects to it. This is a common practice when linking to semantic models created in Azure Analysis Service, in which case dynamic ABC classification is possible, but has the disadvantage of making it difficult to filter and split the data.

Figure 1

The data model used in the example consists of 3 tables, these are the sales table (Fct_Sales), the product table (Dim_Product) and the calendar table (Dim_Calendar).

I would then like information on what classification each subcategory has.
The constructed measure consists of a number of elements:


Classification =
IF(
    HASONEVALUE(Dim_Product[Subcategory]),
    VAR SalesBySubcategory =
        CALCULATETABLE(
            ADDCOLUMNS(
                SUMMARIZE(Fct_Sales, Dim_Product[Subcategory]),
                "@SubcategorySales", [SalesQty]
            ),
        ALLSELECTED(Dim_Product)
        )
    VAR AllSales =
        CALCULATE(
            [SalesQty],
            ALLSELECTED(Dim_Product)
        )
    VAR CurrentSalesQty = [SalesQty]
    VAR CumulatedSales =
        FILTER(
            SalesBySubcategory,
            [@SubcategorySales] >= CurrentSalesQty
        )
    VAR CumulatedSalesQty =
        SUMX(
            CumulatedSales,
            [@SubcategorySales]
        )
    VAR CurrentCumulatedShare =
        DIVIDE(
            CumulatedSalesQty,
           AllSales
        )
    VAR Result =
        SWITCH(
            TRUE,
            ISBLANK(CurrentCumulatedShare), BLANK(),
            CurrentCumulatedShare <= 0.1, "A",
            CurrentCumulatedShare <= 0.5, "B",
            "C"
        )
    RETURN
    Result
)

The measure assumes that the sales levels affecting the analysis are constant and unchanging. First, the sales volume is calculated and then the cumulative share of the sales volume is calculated. The grade obtained depends on its result:

Figure 2

Each sub-category received its grade depending on sales volume.

The first method works dynamically, if a different period is selected, the measure will calculate the classification for that period. However, it has one fundamental disadvantage – it is not possible to easily filter it and use it to categorise the data. It is a measure, so we cannot filter it through the slicer, and the only filtering can be done through the filter bar on the right:

Figure 3

Filters of method one based on classification by measure. As you can see, there is no simple way to select a specific class.

VIII. Second method of dynamic classification ABC

Method two requires editing the semantic model by adding an additional table to it. In method one, the resulting classification is a measure and therefore it is not possible to easily filter it and use it to split the data into smaller fragments. The second approach involves building an additional, static table with ranges at which it is assessed what cumulative share generates what classification.

Figure 4

ABC class analysis ranges

The data model used in method two is larger.

Figure 5

Second method data model

In addition to the tables from method one, the model has two additional tables. These are Dim_Calendar_Sub, this is an additional calendar table that has an inactive relationship between two calendars. The second table is Dict_ABC_Class, which contains the ABC assessment intervals.

The measure used in dynamic evaluation is similar to the measure used in method one, its main difference being the use of a table with ranges that has no relationship between the table and the data model. The relationship is created when the measure is written, and in this situation the evaluation from this table allows the data to be easily filtered and broken down into smaller chunks.


ABCSalesQty = 
VAR SalesbySubcategory =
    CALCULATETABLE(
        ADDCOLUMNS(
            SUMMARIZE(Fct_Sales, Dim_Product[Subcategory]),
            "@Salesbysubcategory", [SalesQty]
        ),
        ALLSELECTED(Dim_Product)
    )
VAR AllSales =
    CALCULATE(
        [SalesQty],
        ALLSELECTED(Dim_Product)
    )
VAR CumulatedShareBySubcategory =
    ADDCOLUMNS(
        SalesbySubcategory,
        "@CumulatedShare",
        VAR CurrentSalesQty = [SalesQty]
        VAR CumulatedSales = 
            FILTER(
                SalesbySubcategory,
                [@Salesbysubcategory] >= CurrentSalesQty
            )
        VAR CumulatedSalesQty =
            SUMX(
                CumulatedSales,
                [@Salesbysubcategory]
            )
        VAR Share =
            DIVIDE(
                CumulatedSalesQty,
                AllSales
            )
        RETURN
            MIN(Share,1)
    )
VAR SubcategoryInClassification =
    FILTER(
        CROSSJOIN(
            CumulatedShareBySubcategory,
            Dict_ABC_Class
        ),
        AND(
            [@CumulatedShare] > Dict_ABC_Class[Lower range],
            [@CumulatedShare] <= Dict_ABC_Class[Upper range]
        )
    )
VAR Result =
    CALCULATE(
        [SalesQty],
        KEEPFILTERS(SubcategoryInClassification)
    )
RETURN
Result

This measure first builds a virtual table calculated in memory, then calculates the cumulative share, again calculates the virtual table by matching the results obtained with the ranges to then calculate the sales volume by class from an unrelated table.

The resulting effect allows the data to be dynamically divided into classes, filtered and the dynamics of change over time to be examined:

Figure 6

The resulting sales volume is linked to the classification and allows the data to be broken down.

The entire Power BI report is viewable via the publish to web option in Power BI. The dataset has been created by script and there is no copyright on it.

IX Conclusion

In summary, Power BI offers flexible solutions for implementing ABC analysis, each designed to meet different needs and constraints. The first approach, which uses measures, is ideal when analysts cannot modify the semantic model. It provides a quick and effective way to calculate ABC categories without changing the data structure. However, its limitations become apparent when data needs to be sliced and diced by ABC categories.

The second approach, using an additional table, offers greater flexibility and reusability. By pre-computing the ABC analysis and incorporating it into the data model, analysts can seamlessly slice and dice data for deeper insights and more robust reporting. Although it requires more up-front effort, this approach is well suited to scenarios where ABC analysis needs to be a reusable and integral part of the analytical workflow.

Ultimately, the choice between these approaches depends on the specific requirements of the analysis, the flexibility available to modify the model, and the long-term goals of the Power BI implementation. Both solutions enable analysts to unlock the value of ABC analysis, making it a powerful tool for prioritising and managing resources effectively.