[Power BI] DAX ALL Function Practical Series ③ Ranking Part 3 : Relative Rank Index

 

This is the third installment of our Ranking Analysis series. In Part 2, we identified market concentration by distinguishing between Top-N and Others. In this part, we will elevate the qualitative value of your ranking data through the "Relative Rank Index."

Simply stating "It's 3rd place" carries a different weight in decision-making than saying "It's a core product within the top 20%." As datasets grow, relative indicators that show a position within the entire set become far more important than absolute ranking numbers.

 

1. Why do we need a 'Relative Rank Index'?


Relative position index

In practice, being 5th place when there are only 10 products is worlds apart from being 5th place among 1,000 products.
  • 5th out of 10 = Average
  • 5th out of 1,000 = Top-tier

In other words, it is difficult to accurately judge a product's strategic position based on absolute rank alone.
  • Fair Comparison: Even if the number of items handled differs by store, objective comparison is possible through "what top percentage it falls into."
  • Visualization of Distribution: It provides an objective standard (Percentile) to define from which point a product belongs to the "core group."
  • Strategic Resource Allocation: By classifying products into Grade A/B/C based on their relative position, you can differentiate marketing budgets or inventory management priorities.


 

2. Reviewing ALL-based Ranking Structure: Why a 'Comparison Set'?


Before calculating the index, let's review the sample data and the basic principles of ranking. Ranking is essentially a battle of "Me (Current Row) vs. Everyone (Comparison Target)," and how you define this "everyone" determines the success of your analysis.

2.1 Sample Dataset for Analysis (Sales Table)

The data we will use is the H1 2024 IT device sales data. It is a structure where stores and products are intricately intertwined, making it tricky to judge performance by simple ranking alone. The dataset includes monthly sales for products like iPhone 15, MacBook Air, Galaxy S24, Pixel 8, and Surface Pro across flagship and mall locations in New York, Los Angeles, Chicago, and Houston.

Monthly sales table by store

2.2 Restoring the Filter Context: The Core of DAX

While it is natural for filters to be applied in general calculations, when ranking, the "targets for comparison" must remain alive in memory even if filters are active. This is where the ALL family of functions comes in.

Restoring the Filter Context : Allselected vs. All

  • ALLSELECTED: Maintains the filters selected by the user in the report (slicers, etc.) but removes filters occurring within the current visualization element (table rows). This is most commonly used in practice.
  • ALL: Ignores even the user's slicer selections to find an absolute global rank.

2.3 Standard Ranking DAX Formula (Best Practice)

The most stable ranking formula in practical datasets uses RANKX combined with ALLSELECTED. By using ISINSCOPE, we ensure the rank is only calculated at the product level, avoiding meaningless ranks in total rows. Using the Dense option ensures there are no gaps in ranking numbers when ties occur.

Rank (ALLSELECTED) =
    IF(
            ISINSCOPE(Sales[Product]), -- Only calculate rank at the Product level (avoid showing rank in Total rows)
            RANKX(
            ALLSELECTED(Sales[Product]),
            [Total Sales], -- Expression used for ranking (measure recommended)
                ,
            DESC, -- Rank in descending order (higher sales = higher rank)
            Dense -- Use Dense ranking (no gaps in ranking numbers when ties occur)
                ),
            BLANK() -- Return blank for Total rows
            )

2.4 Why is this structure powerful?

RANK (Allselected)

Designing based on ALLSELECTED provides the following flexibility:
  • Response to Dynamic Filtering: If you select only 'January 2024' in a slicer, it automatically calculates the 'rank within January' rather than the rank for the entire period.
  • Maintaining Cross-filtering: When filtering for just the 'New York store,' it immediately shows the relative ranking among products sold within that specific store.
  • Sophisticated Denominator Calculation: The Total Product Count—the core of the Relative Rank Index we are learning today—must share this same comparison set to avoid logical errors.

 

3. Core DAX Design: How to Calculate the Relative Rank Index


To obtain the Relative Rank Index, you must divide your current rank by the total number of comparison targets and turn it into a percentage.

How to Calculate the Relative Rank Index : 3 steps

STEP 1: Count Total Targets (Total Count)

You need to know how many total subjects are being ranked. We calculate this using DISTINCTCOUNT of products while applying ALLSELECTED to the product column.

Total Product Count =

    CALCULATE( DISTINCTCOUNT(Sales[Product]),                 ALLSELECTED(Sales[Product]))

STEP 2: Relative Rank Index %

Design it so that a value closer to 1 means the lower tier, and closer to 0 means the top tier (or vice versa depending on your preference). This is done by dividing the current product's rank by the total product count.


Relative Rank Index =

DIVIDE(
    [Rank (ALLSELECTED)], -- Rank of the current product         within the selected context
        [Total Product Count] -- Total number of products in         the comparison group
        )

STEP 3: Assigning Grades Based on Quantiles (Rank Grading)


Based on the index, assign grades that can be used immediately in the field. Using a SWITCH function, you can categorize them:

Product Grade =

VAR R_Index = [Relative Rank Index]
    RETURN
        IF(ISINSCOPE(Sales[Product]),
        SWITCH(TRUE(),
        R_Index <= 0.2, "A (Core)",
        R_Index <= 0.5, "B (Growth)",
        "C (Tail)"
        ),
            BLANK()
            )


 

4. The Power of Relative Ranking Verified by Data


Based on the example data (Total products: 5), we calculate the following:

Total products: 5 result

 

5. Strategic Interpretation: Insights from the Index


① Optimal Resource Allocation Strategy by Grade

Grade A (iPhone 15): The 'pillar' of sales (32.4% of total sales). This is priority zero for inventory securing and marketing budget.

② Performance Diagnosis: Absolute Sales vs. Relative Rank Gap

  • iPhone 15: Proves overwhelming strength in both absolute sales ($685k) and index (20%). However, this also suggests a 'concentration risk' where any slump in this product would be fatal to company-wide performance.
  • Surface Pro: With an index of 100%, there is a five-fold performance difference from the 1st place. The index proves that 'efficiency-centered marketing' targeting a niche market is more suitable than mass advertising.

 

6. Practical Tips: Visualization Guide

Imagine this: a heatmap where colors change according to relative position is much more intuitive than a table simply listing numbers.
  • Conditional Formatting: Set the background color of cells with a Relative Rank Index of 0.2 or less to deep blue, and 0.8 or more to light gray. Anyone looking at the report will immediately see where the core is.
Product performance table_PBI chart
  • Scatter Chart: Place [Total Sales] on the X-axis and 'Relative Rank Index' on the Y-axis.
Bubble chart - Relative Rank Index
  • Since a lower Relative Rank Index value means a higher rank, products located in the bottom-right are 'Core Strategic Products' with both high sales and high ranking.
  • Conversely, products in the top-right have high sales but relatively lower ranking compared to their group, indicating a need to review market structure or the product portfolio.

 

Wrapping up


Adding 'Proportion' to Ranking

Relative rank analysis provides the answer to "How should we layer and manage the assets we have?" If absolute rank is the 'result,' the relative index is the 'compass of strategy.'

Now, try adding a relative Grade next to the simple rank in your reports. The question from management will change from "How much did we sell?" to "Where should we focus?"


What's Next 

"Part 4 - Cross Ranking Analysis (Store × Product)" Product ranking alone is not enough. "Why is it #1 in the NY store but #5 in the LA store?" We will cover how to find hidden opportunities in the 'mismatch' of rankings by crossing stores and products, or regions and categories.

The way to pierce through the field with data continues in the next episode.


<Other posts on the blog>




Comments

Post a Comment

Popular posts from this blog

DAX CALENDAR Function Deep Dive and Practical Usage Guide

Image
The CALENDAR function is essential for creating a date table in a data model, and it plays a vital role when performing time intelligence calculations. Through this document, you'll be able to get a detailed understanding of the CALENDAR function's syntax, how it operates, and how you can leverage it in real-world data analysis scenarios.   1. What Exactly is the CALENDAR Function? The CALENDAR function in DAX is basically your go-to tool for building a dedicated date table based on a specific date range. All you do is give it a start date and an end date, and it spits out a single-column table containing every single date within that range. This generated table is what we commonly call a 'Date Dimension Table. ' It's absolutely critical for your data model because you'll use it to set up relationships with other tables (like sales, orders, or inventory) and, more importantly, it becomes the foundation for all your time-based analysis.     1.1. Syntax ...
Read more

Standard Deviation (Part 2): Strategic Limitations and Complementary Perspectives in Standard Deviation Analysis

Image
As we've seen, standard deviation is more than just measuring 'how spread out the data is' it quantifies that spread to evaluate the stability and risk of business performance, thus serving as a key managerial analysis metric that supports strategic decision-making. However, relying solely on standard deviation for interpretation can easily lead to misjudgment. When using standard deviation for corporate, sales/profit analysis, or forecasting, the following additional factors must be considered: If you missed 'Standard Deviation' Previous contents, Please check out the below link. Standard Deviation The Complete Guide to the Core of Business Data Analysis Standard Deviation (Part 1): Measuring Data Volatility and Using the Insights for Better Strategy   1. Considering the Relationship with the Mean (Expected Value) Standard deviation represents only the absolute degree of data 'spread.' Therefore, to grasp the relative meaning of this figure, it must be cons...
Read more

Standard Deviation (Part 1): Measuring Data Volatility and Using the Insights for Better Strategy

Image
Standard Deviation is a core metric that quantifies the 'dispersion' of your data, showing—as a single number—how tightly clustered or widely spread the data points are from the mean. A larger number means the data is widely spread out, while a smaller number means it's tightly grouped. Let's explore how this concept is used strategically in analyzing sales and profits. If you missed 'Standard Deviation' first contents, Please check out the below link. Standard Deviation The Complete Guide to the Core of Business Data Analysis A nd you can also refer next contents, after reading this. Standard Deviation (Part 2): Strategic Limitations and Complementary Perspectives in Standard Deviation Analysis   1. Why We Must Read the 'Wobble' (The Trap of the Mean) It's natural to first look at average sales or profit when analyzing a company's performance. The average is the central value of the data, making it the most fundamental and useful metric for gr...
Read more