Posts

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

Image
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.   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.   2. Why is Cross Ranking Necessary? While a Global Rank shows the macroscopic market structure, Cross Ranking analysis is a strategic indicator that ca...
Post a Comment
Read more

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

Image
  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'? 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 compar...
Post a Comment
Read more

[Power BI] DAX ALL Function Practical Series ② Ranking Part 2 : Mastering Pareto Analysis - Finding Top-N Structures with DAX ALL + RANKX

Image
The reason we learn the ALL function in Power BI is not just to simply remove filters. It is to establish a Grand Total as a universal reference point and clearly understand the relative position of each item within that context. In the previous post, we looked at the performance structure between products through Mix (Share) and Ranking analysis. In particular, by combining the ALL and RANKX functions, we established a Global Ranking from a company-wide perspective and verified what position each product occupies within the overall sales structure. In this post, we intend to go one step further and expand the horizon of our analysis. The protagonist is Pareto analysis, which uncovers the point of strategic decision-making by using the ALL function to clear filters for "Share," the RANKX function to grant "Order," and then "Accumulating" these results. This process of finding the basis for "Selection and Concentration" beyond a simple listing of...
Post a Comment
Read more

[Power BI] DAX ALL Function Practical Series ② Mastering Ranking Part 1 : Ranking Analysis, Top-N, Pareto Analysis

Image
In Power BI, ranking analysis goes beyond simple data sorting; it is a vital method for understanding market structures. While a simple sales report focuses on the past record of "how much was sold," ranking analysis answers more critical questions: Which of our products is the number one bestseller? What percentage of total sales do the top 20% of products generate? How much has this month's rank changed compared to last month? How does the ranking of popular products differ from store to store? To answer these questions, you must use the RANKX function in conjunction with the ALL function. The ALL function plays an especially crucial role in ranking analysis because it restores data hidden by filters, creating a "complete competitive landscape that includes invisible competitors." Power BI Ranking Analysis Series This series consists of five practical analysis parts: Part 1 — Global Ranking (RANKX + ALL) -- Current Post Part 2 — Top-N & Others (Pareto) Par...
Post a Comment
Read more

[Power BI] DAX ALL Function Practical Series ① Mastering Mix & Share (Market Share & Contribution Analysis)

Image
In the previous post, we summarized the two faces of the ALL function: the Filter Modifier and the Table Provider. Today, as our first practical pattern, we will master "Mix & Share" analysis—the most widely used technique in the field—and the design of Performance Indices derived from it. The purpose of this post is clear: "When you calculate shares accurately, your sales strategy changes." This is not just a list of formulas. It is a full-course guide starting from DAX design based on actual sales data, moving to numerical verification, and ending with strategic interpretation.   1. Why is Mix Analysis Important? 1.1 Mix is Not Just a Simple Percentage  Simple sales figures are merely results, but "Share" is a map for designing the future. Through Mix analysis, we obtain management answers to the following questions. We calculate share for one reason: to see the structure. Store Mix: How much does each store contribute to total sales? Product Mix:...
Post a Comment
Read more