Posts

[Power BI] DAX ALL Function Practical Series ③ Accumulation Part 1: Basic Running Total

Image
In our last post, we theoretically explored how the ALL function releases time filters to set the stage for accumulation analysis. Today, we move into the practical application: implementing a Running Total using real-world IT device sales data and interpreting it through the lens of business strategy. If your professional dashboard only shows monthly sales charts, it’s like checking only the "instantaneous speed" of a car. A Running Total, however, is the "odometer" that shows how far you have traveled toward your destination. Especially in organizations with annual quotas, accumulation data is more than just a performance report—it is the most powerful basis for deciding whether to commit additional resources in the second half of the year.   1. Practical Example Data Structure We will design basic measures based on the provided July to December data. Base Measure: Total Sales = SUM(Sales[Sales]) Total Volume: $3,259,000 (Total for the second half)   2. Why ...
Post a Comment
Read more

[Power BI] DAX ALL Function Practical Series ③ Accumulation Analysis Series Guide

Image
In our previous post, we explored how the ALL function restores the comparison set for ranking analysis. Today, we begin the [Power BI Accumulation Analysis Series], often referred to as the "highlight" of practical data analysis. Beyond simply adding numbers, this is a strategic analytical method for interpreting how far we have run toward our target (Run-rate) by controlling the flow of time. When building dashboards in the field, you will often find that a single accumulation chart tells a much more compelling story than simple monthly sales figures. In this post, we will first outline the overall structure and core principles of the upcoming Power BI Accumulation Analysis series.   1. The Essence of Accumulation Analysis: Why the ALL Function? 1.1 Accumulation is the act of reviving 'hidden time'  The moment you click on "March" in a Power BI report, the data for January and February disappears from the screen due to filters. However, since a Running T...
Post a Comment
Read more

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

Image
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. Power BI Ranking Analysis Series This series consists of five practical analysis parts: Part 1 — Global Ranking (RANKX + ALL)  Part 2 — Top-N & Others (Pareto) Part 3 — Relative Rank Index   Part 4 — Cross Ranking (Store × Product)  Part 5 — Rank Change Analysis  -- Current Post   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.   2. Why is Rank Change Analysis Important? Strategically, "who is climbing up"...
Post a Comment
Read more

[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. Power BI Ranking Analysis Series This series consists of five practical analysis parts: Part 1 — Global Ranking (RANKX + ALL)  Part 2 — Top-N & Others (Pareto) Part 3 — Relative Rank Index   Part 4 — Cross Ranking (Store × Product)  -- Current Post Part 5 — Rank Change Analysis   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 produc...
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. Power BI Ranking Analysis Series This series consists of five practical analysis parts: Part 1 — Global Ranking (RANKX + ALL)  Part 2 — Top-N & Others (Pareto) Part 3 — Relative Rank Index  -- Current Post Part 4 — Cross Ranking (Store × Product) Part 5 — Rank Change Analysis   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 plac...
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