Select the Top 5 projects

Kareem77

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?

Kareem77

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

FBergamaschi

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

v-nmadadi-msft

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.

wardy912

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)

DataNinja777

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,