Next-Level Power BI Visual Calculations with Offset, Index, Rank, RowNumber, And Rankx
In my previous blog, I discussed the new feature of Visual Calculation. In this blog, I will be showing you more functions that you can use in visual calculations.
You can find the last blog here
A quick recap
What are Visual Calculations in Power BI?
Visual calculations are DAX calculations defined and executed directly on a visual within Power BI. Unlike traditional calculations that are stored and processed in the data model, visual calculations are applied directly to the data presented in the visual, offering a new layer of flexibility and performance. This feature simplifies the DAX required for dynamic calculations, leading to easier maintenance, better performance, and more intuitive data exploration.
Power BI Functions Offset and Index are released in December 2023 and Rank and RowNumber are released in April 2023. All these functions and window functions provide functionality like a very similar SQL WINDOW function with the facility of Order by and positioned by. In this blog, I will try to showcase to you how can we use them in visual calculation.
You can watch them in action in this video
Model Used
In my videos and blogs, I often refer to the standard sales model that we use. It is a well-structured and comprehensive Power BI model that enables us to analyze sales data across various dimensions. The model revolves around the Sales fact table, which contains retail sales transactions. In this model, there are several components and relationships that we will explore in more detail:
Tables Used
Sales Fact Table: The core of this model, contains detailed records of retail sales transactions. Each row represents a sale, and it includes several key columns such as Order No,Sales Date, Item ID, City ID, Customer ID, Gross Amount, Discount Amount, and Net Sales. The key measure we focus on is:[Net], which represents the net sales amount calculated as Gross Amount - Discount Amount. This measure provides insights into the actual revenue generated after discounts are applied.
Dimension Tables: These tables provide context and additional details to the sales data, allowing for more nuanced analysis:
- Item: Contains information about the items sold, including
Item ID,Item Name,Category, andBrand. It enriches the sales data by detailing what is being sold. - Geography: Provides geographical context with fields like
City ID,City Name, andState. This dimension helps in analyzing sales distribution and performance across different locations. - Date: Includes date-related information such as
Date,Month,Quarter, andYear. This dimension is crucial for time-based analysis, allowing for trend analysis and period comparisons. - Customer: Contains customer-specific details such as
Customer ID,Customer Name,City, andState. This dimension enables analysis of sales performance and behavior across different customer segments.
Relationships
The relationships between the Sales fact table and the dimension tables are defined as follows:
- Sales — Sales Date -> Date [Date]: This relationship links each sale to a specific date, facilitating time-based analysis and enabling the use of time intelligence functions in DAX.
- Sales [Item ID] -> Item [Item ID]: Connects sales transactions to the specific items sold, allowing for item-level analysis and category performance reviews.
- Sales [City ID] -> Geography [City ID]: Associates sales records with geographical locations, enabling regional sales analysis and geographic performance insights.
- Sales [Customer ID] -> Customer [Customer ID]: Links sales transactions to individual customers, supporting customer-centric analysis.
The relationships between the Sales fact table and the dimension tables are set as many-to-one (⋆:1), where the ‘many’ side is on the Sales fact table and the ‘single’ direction is towards the dimension tables. This setup allows for effective aggregation and filtering of sales data based on the attributes of the corresponding dimensions. This ensures that the analysis can be as detailed or as general as necessary.
Let’s take examples of each function to understand how they can be applied within Power BI visual calculations.
Offset — Move Across Rows- Relative Value
The Offset DAX function retrieves a row situated a specific number of positions away from the current row within the same table, either preceding or following it, as specified by the given offset(delta). In the normal calculation, it is driven by Relation columns. Here in the case of Visual calculation, we can use ROWS(AXIS) as a relation to make it more dynamic. Also, we will be able to use the RESET parameter
Trail by a Row= CALCULATE([Net], OFFSET(-1,ROWS))
Trail by a Row Reset at HIGHESTPARENT = CALCULATE([Net],OFFSET(-1,ROWS,,,,,HIGHESTPARENT))
Trail by a Row using measure sort = CALCULATE([Net], OFFSET(-1, ROWS, ORDERBY([Net], DESC)))This is more flexible than the previous and next.
For the next one, we need a Matrix visual. You can check how Reset is happening across HIGHESTPARENT
You can even sort on the measure, which was not an option in the previous and next as of now. Best to use Table visual as we can see sorting on measure when there is more than one axis.
Index — Get Top/Bottom performer. How about third topper
Index = CALCULATE([Net], INDEX(3,ROWS,ORDERBY([Net], DESC)))//Thrid Topper
Index 1 = CALCULATE([Net], INDEX(1,ROWS)) //Topper by ROWThe Index DAX function delivers a row from a specified partition(Reset can used for partition), located at a precise position determined by the position parameter, and arranged according to the specified order. In the normal calculation, it is driven by Relation columns. Here in the case of Visual calculation, we can use ROWS(AXIS)as a relation to make it more dynamic.
Third topper by Net
The same formula works when you change the Axis
Topper by Axis/ROW
Rank — The New Rank DAX function calculates the rank of the current context within a given partition, ordering it as specified. If no matching rank is identified, the result is returned as blank. In the normal calculation, it is driven by Relation columns. Here in the case of Visual calculation, we can use ROWS(AXIS) as a relation to make it more dynamic. Also, we will be able to use the RESET parameter
RANK By Net= RANK(DENSE, ROWS, ORDERBY([Net],DESC))
Rank by Axis = RANK(DENSE, ROWS)
RANK with Reset = RANK(DENSE, ROWS,,,,LOWESTPARENT)Rank by Net measure
Rank by Axis
When you use reset option - RANK(DENSE, ROWS,,,,LOWESTPARENT)
RANKX — The DAX RANKX function is designed for ranking values in datasets, commonly used in Power BI. It primarily assigns ranks based on value comparison, supporting both ascending and descending orders. The function requires specifying the target table and the values or expressions to rank. Additionally, it provides options to handle ties through “DENSE” or “SKIP” settings, ensuring flexible ranking outcomes. Rankx is not far behind on visual calculation. In place of Table, you can use ROWS, and you can use rankx to calculate the flexible rank.
Rankx = RANKX(ROWS,[Net],,DESC,Dense)
ROWNUMBER: The ROWNUMBER DAX function assigns a unique rank to each row in the current context within a specified partition, organized according to a defined order. If a corresponding match cannot be determined, the row number is returned as blank. In the normal calculation, it is driven by Relation columns. Here in the case of Visual calculation, we can use ROWS(AXIS) as a relation to make it more dynamic. Also, we will be able to use the RESET parameter
RowNum 1 = ROWNUMBER(ROWS,ORDERBY([Sum of Quantity],DESC))Rownumber are assigned based on the available Axis/Row Columns
It changes when you change the Row/Axis columns in the visual.
You can get the file at
https://github.com/amitchandakpbi/powerbi/raw/main/Visual%20Calculations.pbix
Visual calculation is a great tool that not only makes calculations quicker and easier but also offers more flexibility in terms of time. With visual calculation, you can use the same calculation even when the row or axis changes, which means you'll need fewer calculations overall.
Release Blog
Microsoft Documentation
Complete Power BI in one Video in 11 hours
Mastering Power BI: 230+ Videos
Expertise Power BI: 150+ Videos
Power BI 50 Interview Questions and 10 Advanced Use Cases
My Medium blogs can be found here if you are interested
Click here to access all my blogs and videos in a jiffy via an exclusive glossary using Power BI. Please like, share, and comment on these blogs. I would appreciate your suggestions for improvement, challenges, and suggestions for topics so that I can explore these in more depth.
In addition, I have over 750 videos on my YouTube channel that are related to Power BI, Tableau, and Incorta. With the help of these videos, you will learn hundreds of DAX and Power Query functions, in addition to hundreds of Power BI, use cases that will assist you in becoming an expert in Power BI. Make sure you subscribe, like, and share it with your friends.
Master Power BI
Expertise in Power BI
Power BI For Tableau User
DAX for SQL Users
Learn SQL
Don’t forget to subscribe to
and join our Power BI community
