[Power BI] DAX ALL Function Practical Series ② Ranking Part 4: Cross Ranking Analysis (Store × Product)


In Part 3, we categorized the strategic grades of products. Now, it is time to combine the contexts of Store and Product. This is because a product that is number one company-wide might actually be the worst performer in a specific store, or a small store might achieve top rank in a specific category.

In this Ranking Part 4, we will use the ALL family of functions to master the design of "Cross Ranking," which intersects the contexts of stores and products.

Store * Product Matrix

 

1. Analysis Data Overview: H1 2024 IT Device Sales

  • Stores: New York (NY), Los Angeles (LA), Chicago, Houston
  • Products: iPhone 15, MacBook Air, Galaxy S24, Pixel 8, Surface Pro
We analyze sales data based on 4 stores × 5 major product lines. The dataset includes monthly records for 2024, tracking sales amounts for each product across these flagship and mall locations.

Monthly sales data by store

 

2. Why is Cross Ranking Necessary?


While a Global Rank shows the macroscopic market structure, Cross Ranking analysis is a strategic indicator that captures regional consumption patterns and store-specific anomalies.

Typical use cases include:

① Product Ranking within a Store 

Store managers can use this information to make critical decisions: "What are our customers most enthusiastic about?" → Leading to optimized display and inventory.

② Store Ranking by Product 

The headquarters marketing team can establish strategies such as: "Where is the 'holy land' for this specific product?" → Leading to optimized logistics distribution and regional marketing targeting.

Ultimately, Cross Ranking analysis is a key analytical tool that connects corporate strategy with field operations.

 

3. DAX Design: Shifting the Context


The core of Cross Ranking is "what to ignore and what to leave behind." The three main functions used for this are:
  • ALL: Removes all filters.
  • ALLEXCEPT: Maintains filters only on specific columns.
  • ALLSELECTED: Maintains filters selected by the user.

In practical dashboards, ALLSELECTED is the primary choice because responsiveness to slicers is essential.


[All vs. ALLSELECTED] coming soon

[ALL function (All vs ALLSELECTED vs. ALLEXCEPT)] coming soon


3.1 Rank within Store "What is the rank of this product's performance within our specific store?"

  • Strategic Value & Utilization:
Field Optimization: It serves as an objective indicator for identifying unique consumption patterns of a specific store to determine display positions and stock levels.
Performance Diagnosis: If a company-wide #1 product is underperforming in a specific store, it serves as a key KPI helping store managers decide whether to check the display status or local competitor activity.

Pro Tip: What happens if you forget ISINSCOPE? 
The most common mistake for beginners is omitting ISINSCOPE. If you leave it out, the "Total" row of a matrix will always display a rank of 1. This is because the total amount only compares itself to itself, making it #1. To keep your report clean, always ensure the calculation is controlled to run only at the "Product" level.

3.2 Rank within Product "Which store in the country sells this product the best?"

  • Strategic Value & Utilization:
Visualizing Competitive Structure: By comparing performance across stores for a specific product, you can grasp the sales competitiveness of each store at a glance.
Logistics & Marketing: It serves as the core evidence for logistics optimization—such as prioritizing inventory for stores with rising ranks—and for measuring regional promotion efficiency. This is essential for sophisticated headquarters marketing strategies.

 

4. ALL vs. ALLSELECTED: Practical Differences


While we used ALL for absolute ranking in Parts 1 and 2, ALLSELECTED is the correct answer for dashboards where user interaction is important.

ALL vs. ALLSELECTED: Practical Differences

Why ALLSELECTED?

If you select only [Samsung, Apple] in a slicer:
  • ALL might still show a product as #4 because it includes invisible products.
  • ALLSELECTED recalculates the rank as #1 or #2 within that selected list. This maintains the analysis context set by the user and prevents confusion in data interpretation.

 

5. Practical Data Analysis: Store × Product Matrix


Comparing the cross-rankings of New York and Chicago:

Comparing the cross-rankings of New York and Chicago

New York Flagship

  • Insight: In New York, the local rankings mirror the global rankings. New York's performance essentially determines the overall corporate trend. Any drop in rank here could severely impact company-wide revenue, making these Local Rank fluctuations the most sensitive KPIs for headquarters.

Chicago Central

  • Insight: While Apple products are concentrated in New York, Chicago acts as a stronghold for the Android camp. Even if the headquarters runs iPhone-centric integrated marketing, they should allocate a separate budget for Galaxy promotions specifically for the Chicago branch.

 

6. Conditional Formatting in Matrix Visualizations


Store * Product Matrix

Once you have calculated Cross Rankings, utilize [Conditional Formatting] -> [Background Color] in your Matrix visualization.
  • Rule: Set a deep red for a "Rank within Store" of 1, and lighter colors as the rank decreases.
  • Effect: This creates a Heatmap that allows you to see at a glance which category is the "star player" for each store.

 

Wrapping up


Cross Ranking analysis is the "redefinition of performance based on context."
  • Global Rank shows the overall market dominance of the company.
  • Cross Rank allows for the establishment of customized tactics suitable for the field (store/region).

By freely combining RANKX and the ALL family of functions, you can accurately locate the position of data under any complex filter situation.

What’s Next?

Part 5 — Rank Change Analysis "Why did a product that was 5th last month rise to 2nd this month?" We will move beyond simple current rankings to learn how to calculate and visualize rank trends over time using arrow-based dashboards.



<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