# ROWNUMBER

## What is the ROWNUMBER Function in DAX?

The ROWNUMBER function in DAX returns the rank of a row within a table, with a specified ordering. It assigns a unique number to each row, starting from 1 for the first row, and incrementing by 1 for each subsequent row. This function is useful for creating running totals, identifying top or bottom performers, and ranking data based on certain criteria.

## Syntax of the ROWNUMBER Function

The syntax for the ROWNUMBER function is as follows:

ROWNUMBER([Order_Column], [Order_Ascending])

``` - `Order_Column`: This is the column by which the table is ordered. You can use any column in the table, but it's usually a column with numerical or alphabetical values that you want to rank. - `Order_Ascending`: This is an optional parameter that specifies whether the table is ordered in ascending or descending order. The default value is `ASC`, which means the table is ordered in ascending order. You can also use `DESC` for descending order. Example of Using the ROWNUMBER Function Let's say you have a table called `Sales` that contains the following columns: `Product`, `Salesperson`, and `SalesAmount`. You want to rank the salespeople by their sales amount, and assign a unique number to each row. Here's how you can use the ROWNUMBER function to achieve this: ```

Sales Rank = ROWNUMBER(Sales[SalesAmount], DESC)

``` This formula tells Power BI to order the `Sales` table by the `SalesAmount` column in descending order, and assign a unique number to each row based on this order. The resulting table will have a new column called `Sales Rank`, which contains the rank of each salesperson based on their sales amount. Using ROWNUMBER with Other DAX Functions The ROWNUMBER function can be combined with other DAX functions to create more complex calculations. Here are a few examples: Running Total You can use the ROWNUMBER function to create a running total of a measure, such as sales amount. Here's an example formula: ```

Running Total = CALCULATE(SUM(Sales[SalesAmount]), FILTER(ALL(Sales), ROWNUMBER(Sales[SalesAmount], DESC) ❰= MAX(ROWNUMBER(Sales[SalesAmount], DESC))))

``` This formula tells Power BI to calculate the sum of the `SalesAmount` column for each row, up to and including the current row based on its rank. The `MAX` function is used to get the maximum rank of the current row. The `FILTER` function is used to apply the calculation to all rows in the table. Top N Items You can use the ROWNUMBER function to identify the top or bottom N items based on a certain criteria. Here's an example formula: ```

Top 5 Products = CALCULATE(SUM(Sales[SalesAmount]), TOPN(5, VALUES(Sales[Product]), ROWNUMBER(Sales[SalesAmount], DESC)))

``` This formula tells Power BI to calculate the sum of the `SalesAmount` column for the top 5 products, based on their rank in descending order. The `TOPN` function is used to select the top 5 products based on their sales amount. The ROWNUMBER function in DAX is a powerful tool for assigning a unique number to each row in a table, and ranking data based on certain criteria. It can be used in combination with other DAX functions to create more complex calculations and enhance your Power BI reports. By understanding how to use the ROWNUMBER function, you can take your data analysis skills to the next level. Power BI DAX Training Courses by G Com Solutions (0800 998 9248) Power BI DAX Intensive Training Course £1,260.00 – £31,860.00 Select optionsContinue Loading Done Power BI DAX Introduction £474.00 – £11,700.00 Select optionsContinue Loading Done Power BI DAX Intermediate £474.00 – £11,700.00 Select optionsContinue Loading Done Power BI DAX Advanced £474.00 – £11,700.00 Select optionsContinue Loading Done Upcoming Courses 15-17 July 202416-18 September 202411-13 November 2024 Contact Us Subject Your Name (required) Company/Organisation Email (required) Telephone Training Course(s) Power BI Intensive TrainingPower BI introduction Power BI IntermediatePower BI AdvancedDAXPower Query MPower BI CertificationPower BI AdministrationPower PlatformPower AutomatePower AppsOTHER Your Message Upload Example Document(s) (Zip multiple files) ```
``` ```
``` ```
``` ```
``` ```
``` ```