[Power BI] DAX ALL Function Practical Series ② Ranking Part 5: Rank Change Analysis


If you mastered Cross Ranking—which weaves together the contexts of stores and products—in Part 4, it is now time to add the flow of time.

"Why did a product that was 5th last month rise to 2nd this month?"

The direction and speed of change are often more important than the static position of the data. In this Part 5, we will conquer the core DAX logic for calculating month-over-month rank fluctuations and implementing an arrow-based dashboard.

New York Store Rank Change Case

1. Analysis Data Overview: H1 2024 IT Device Sales

The data we are analyzing consists of monthly sales records for 5 major product lines across 4 stores.

Monthly sales table by store


2. Why is Rank Change Analysis Important?


Strategically, "who is climbing up" is far more important than simply "who is in 1st place."
  • Early Trend Detection: Products with low total sales but rapidly rising ranks are targets where you should concentrate your marketing firepower.
  • Crisis Signal Detection: If a product that was an immovable #1 starts slipping to 2nd or 3rd, you should investigate competitive offensives or changes in the Product Life Cycle (PLC).
  • Intuitive Reporting: Arrow icons (▲/▼) convey the "temperature" of the field much faster than plain text.

3. DAX Design: Fetching the Previous Month's Rank


To calculate the rank change, you must compare the [Current Rank] with the [Previous Month's Rank].


3.1 Current Month Rank (CM Rank)

This is the basic rank for the currently selected point in time. We use RANKX combined with ALLSELECTED to calculate the rank of products based on total sales, ensuring it only calculates at the product level using ISINSCOPE.

CM Rank =
    IF(ISINSCOPE('Sales'[Product]),
            RANKX(ALLSELECTED('Sales'[Product]), 
            [TotalSales], DESC, Dense),
            BLANK()
            )

3.2 Previous Month Rank (PM Rank)


By combining SELECTEDVALUE and CALCULATE, we forcibly create a filter context for one month ago. This involves clearing the existing time filters for Month and Year and then locking the filter to the previous month of the same year. This allows us to retrieve what the rank was in the prior period.

PM Rank =
 VAR CurrentMonth = SELECTEDVALUE('Sales'[Month])
 VAR CurrentYear = SELECTEDVALUE('Sales'[Year])
     RETURN
       CALCULATE(
        [CM Rank],
        ALL('Sales'[Month], 'Sales'[Year]),
            'Sales'[Month] = CurrentMonth - 1,
            'Sales'[Year] = CurrentYear
            )

3.3 Rank Change

Since a smaller ranking number represents a higher position, we perform the calculation: [Previous Month Rank - Current Month Rank]. A positive result (+) indicates a rise in rank, while a negative result (-) indicates a fall.

Rank Change =
    IF(NOT ISBLANK([PM Rank]) 
        && NOT ISBLANK([CM Rank]),
        [PM Rank] - [CM Rank],
        BLANK()
            )

4. The Highlight of Visualization: Implementing Icon Logic


Once the rank change value is calculated, you must add visual cues so users can understand the data at a glance.

4.1 Setting the Variation Icons 

Using a SWITCH function, we can assign specific symbols based on the rank change:

Rank Icon =
    VAR Change = [Rank Change]
        RETURN
        SWITCH(TRUE(),
            Change > 0, "▲",
            Change < 0, "▼",
            Change = 0, "─",
            BLANK()
            )

4.2 Utilizing Conditional Formatting

  • Positive Change (Rise): Green (Indicates improved performance)
  • Negative Change (Fall): Red (Indicates a need for attention)


5. Practical Data Analysis: New York Store Rank Change Case


Let’s simulate the rank changes for the New York store between the transition of periods.

New York Store Rank Change Case

Strategic Analysis Based on Rank Changes:

① MacBook Air: An Overwhelming Comeback (+4 Rise)

  • Analysis: The MacBook Air, which was at the bottom (5th) in the previous period, rose vertically to 1st place company-wide in the current month. This means more than just a sales increase; it signifies that market interest has shifted completely from smartphones to high-performance laptops.
  • Insight: A jump of four places is an extraordinary phenomenon. You should verify whether a specific promotion hit the mark or if there were large corporate bulk purchases, and then review if this success formula can be applied to the following month.

② Surface Pro: Steady Upward Trend (+1 Rise)

  • Analysis: It climbed one step from 4th to 3rd. While not explosive, it is steadily expanding its share, surpassing the Galaxy S24 and settling into the upper tier.
  • Insight: Even a slight rise (▲1) is a signal that the product line is beginning to gain a "relative advantage" in the market.

③ iPhone 15 & Galaxy S24: Simultaneous Dip in Top Tier (-1 Fall)

  • Analysis: The iPhone 15, which was 1st, dropped to 2nd, and the Galaxy S24, which was 3rd, dropped to 4th.
  • Insight: For both products, this is interpreted as a shift in relative ranking because the "pie" for the PC/Tablet category grew during this month, rather than a failure in absolute performance. A 'variation of -1' is more reasonably viewed as market seasonality rather than a crisis.

Wrapping up


Rank Change Analysis gives a "story" to your data.
  • You define a static position with RANKX.
  • you call upon your "past self" using Time Intelligence functions for comparison.
  • You deliver immediate insights to management through icons and colors.

The true power of Power BI lies in making these invisible trends visible.




<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