topic Re: Select the Top 5 projects in DAX Commands and Tips

Select the Top 5 projects

Kareem77 — Wed, 25 Jun 2025 13:56:36 GMT

Hi,

I am trying to get top 5 projects through a DAX measure.

The data is as follows:

1. Revenue Table has columns projectID, Project Name, Customer, Region, Month, Revenue

2. COGS Table has Project ID, Month, COGS which stands for cost of goods sold

3. Projects Table which has Project ID, Project Manager, Start date, End date, Status which has all projects set to active

There are two measures, one which calculates the Gross margin by subtracting the total COGS from the total Revenue and gross margin percentage which divides the gross margin over by the revenue

I want to get the Top 5 projects by Gross Margin and by Revenue. Initially I looked into TOPN:

Top 5 Projects by Gross Margin = TOPN(5,Projects,[Gross Margin],DESC)

Top 5 Projects by Revenue = TOPN(5,Projects,[Total Revenue],DESC)

and created tables with it, but I realized I needed it in DAX form so that it would return the following output:

Project ID	Rank
PRJ1	1
PRJ4	2
PRJ5	3
PRJ2	4
PRJ3	5

Or just the projects table sorted by top 5 would also work.
Anyone know how to go on about this?

Re: Select the Top 5 projects

DataNinja777 — Wed, 25 Jun 2025 15:04:54 GMT

Hi Kareem77 ,

To identify the top 5 projects, you will first need to establish your base measures. These measures calculate the total revenue, total cost of goods sold, and the resulting gross margin. These form the foundation for any subsequent ranking calculations.

Total Revenue = SUM(Revenue[Revenue])

Total COGS = SUM(COGS[COGS])

Gross Margin = [Total Revenue] - [Total COGS]

One effective method to get your desired output is to create a new calculated table within your data model. This approach generates a static table containing only the top 5 projects ranked by a specific measure. This can be particularly useful for summary pages or when you need a fixed list of top performers. The TOPN function is combined with ADDCOLUMNS and RANKX to first identify the top projects and then assign them a specific rank.

For the Top 5 projects ranked by Gross Margin, you can use the following DAX expression to create a new table. This code snippet first finds the top 5 projects based on the [Gross Margin] measure and then adds a "Rank" column to display their standing.

Top 5 Projects by Gross Margin = ADDCOLUMNS ( TOPN ( 5, VALUES ( Projects[Project ID] ), [Gross Margin], DESC ), "Rank", RANKX ( ALL ( Projects[Project ID] ), [Gross Margin],, DESC ) )

Similarly, you can create another calculated table to display the Top 5 projects based on Total Revenue. The logic is identical, but it references the [Total Revenue] measure instead of the gross margin.

Top 5 Projects by Revenue = ADDCOLUMNS ( TOPN ( 5, VALUES ( Projects[Project ID] ), [Total Revenue], DESC ), "Rank", RANKX ( ALL ( Projects[Project ID] ), [Total Revenue],, DESC ) )

An alternative, more dynamic approach involves creating a ranking measure that can be used within any visual in your report. This method does not create a physical table but calculates the rank on the fly, allowing it to respond to other filters and slicers in your report. You would create a measure to rank projects and then apply a filter on a table or matrix visual to display only the top 5.

To implement this, you can write a ranking measure for Gross Margin. The use of HASONEVALUE ensures the rank is calculated only at the project level, and ALLSELECTED allows the ranking to adjust based on active filters in the report.

Gross Margin Rank = IF ( HASONEVALUE ( Projects[Project ID] ), RANKX ( ALLSELECTED ( Projects[Project ID] ), [Gross Margin],, DESC, Dense ) )

After creating this measure, you would add Project ID and the [Gross Margin Rank] measure to a table visual. Then, in the visual's filter pane, you would set a filter on Gross Margin Rank to show only values that are "less than or equal to 5". This provides a flexible view of the top projects that can change dynamically as a user interacts with a dashboard, for example, by selecting a specific region or time period.

Best regards,

Re: Select the Top 5 projects

wardy912 — Wed, 25 Jun 2025 15:08:34 GMT

Hi Kareem77

You could add 2 measures using RANKX

Project Rank by Gross Margin = RANKX( ALL('Projects'[Project ID]), [Gross Margin], , DESC, Dense )

Project Rank by Revenue = RANKX( ALL('Projects'[Project ID]), [Total Revenue], , DESC, Dense )

Then add the 2 measures to a table, apply filters to the visual:

Project Rank by Gross Margin is less than or equal to 5

If preferred, you could add a calculated table:

Top 5 Projects by Gross Margin Table = VAR RankedProjects = ADDCOLUMNS( 'Projects', "Rank", RANKX(ALL('Projects'), [Gross Margin], , DESC, Dense) ) RETURN FILTER(RankedProjects, [Rank] <= 5)

Re: Select the Top 5 projects

Kareem77 — Wed, 25 Jun 2025 16:00:48 GMT

Thanks everyone. Seems like exact way I want isn't directly doable purely in DAX, but with other ways.

Re: Select the Top 5 projects

v-nmadadi-msft — Thu, 26 Jun 2025 12:23:32 GMT

Hi Kareem77 ,

May I ask if you have resolved this issue with advice provided by wardy912 and DataNinja777 ? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster. If not please let us know the update on your issue so that we can look into it again.

Thank you.

Re: Select the Top 5 projects

FBergamaschi — Thu, 26 Jun 2025 13:31:07 GMT

Hi Kareem77

it is surely doable in DAX, please provide dummy sample data in tables we can import in Power BI and make sure you get the righe result

Re: Select the Top 5 projects

v-nmadadi-msft — Mon, 30 Jun 2025 04:10:26 GMT

Hi Kareem77

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If our responses has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

Re: Select the Top 5 projects

v-nmadadi-msft — Fri, 04 Jul 2025 04:25:21 GMT

Hi Kareem77

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the community members for the issue worked. If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Thanks and regards

Re: Select the Top 5 projects

Poojara_D12 — Sat, 05 Jul 2025 05:30:52 GMT

Hi Kareem77

To calculate the top 5 projects by Gross Margin or Revenue using DAX and return them in a structured form (like showing the Project ID and Rank in a table), you can use the RANKX function instead of TOPN directly, as TOPN returns a table and cannot be used directly in a measure that outputs a scalar value. Start by creating the base measures for Total Revenue, Total COGS, and Gross Margin. Then, use RANKX to create a measure that ranks each project based on the chosen metric. For example, RANKX(ALL('Projects'[Project ID]), [Gross Margin], , DESC, DENSE) will rank all projects by their Gross Margin in descending order. After that, create a filtering measure that checks whether a project’s rank is less than or equal to 5. This flag can then be used in a table visual filter to display only the top 5 projects. This approach keeps the logic dynamic and reusable across visuals without manually filtering each table. It also ensures you can present both the Project ID and its rank, which is useful for reporting or export scenarios.