Implementation of Automatic Selection of Assortment Matrix for Category Analysis in Power Bi
Year: 2025
Type: article
Abstract: The article presents a methodology for the formation of a multicategory assortment matrix and a comprehensive approach to automating the selection of assortment matrices for retail outlets with limited shelf space. Using Power BI tools and the DAX language, an algorithm was developed to identify the optimal set of SKUs considering product ranking, category structure, and client space constraints. The study details the implementation logic of the strategic vision of the company, the mathematical model, the construction of tables, and the DAX calculations, including two key DAX measures designed to apply different assortment constraints. The first measure evaluates the status of each SKU within the category, taking into account its ranking and category limit, the available shelf space, and actual assortment allocation, thereby providing decisions on whether an item should be included, excluded, or marked for review. The second measure assesses overall SKU status at the store level, considering aggregated category values and adjusted limits, ensuring that high-priority items are retained while low-priority or restricted-status SKUs are not proposed. The study illustrates Power BI capabilities as an optimization analytics platform and demonstrates practical aspects for implementing data-driven decision-making in category management, including rules for excluding items with special statuses, such as NPI or DEL, from the assortment matrix, ensuring both efficiency and strategic alignment with company goals. Furthermore, the article highlights the integration of demand data, ranking logic, and categorical constraints into simple, but effective dashboards, enabling managers and analysts to proactively monitor decision outcomes and adjust assortments according to dynamic market conditions. The approach proposed provides an innovative model for balancing economic performance with operational constraints, offering a practical framework for retailers and distributors facing limited shelf capacity while maximizing customer choice and store profitability. The paper also considers methodological implications for designing adaptive data-driven tools for assortment optimization, illustrating how mathematical formulations can be translated into intuitive and actionable DAX metrics. Power BI thus serves as a powerful platform for implementing automated assortment strategies, enhancing decision quality, and increasing managerial efficiency in category management applications across different retail contexts. The study contributes both practically and theoretically by taking a classic problem of assortment planning and addressing it through systematic automation, providing examples of DAX calculations and model structure that can be replicated in a multi-category retail environment. The findings demonstrate that the integration of ranking, category structure, and space constraints into the decision logic can improve assortment effectiveness, reduce managerial workload, and ensure that priority SKUs are made available to shoppers while restricted or low-performing products are appropriately managed. This research provides a novel framework for applied retail analytics and category management, bridging mathematical modeling, data-driven decision-making, and practical implementation through Power BI and DAX-based tools.
Authors: M. P. Chaikovska, V. M. Stoianov
Institution: Odesa I. I. Mechnikov National University
Language: English
Subfield: Management Information Systems
Domain: Social Sciences
Open Access status: diamond
OpenAlex ID: W7147090715