Correcting DAX Code to Get Top 5 Products by Total Profit

The DAX code you've provided is attempting to get a table of sales for the top 5 products based on total profit but it will not yield the expected results because RANKX function must be used inside a measure.

RANKX requires a row context to function correctly. However, inside a CALCULATETABLE, there is no inherent row context, thus RANKX will not be able to determine a ranking for each product correctly.

To get your desired result, you need to first create a measure that calculates the total profit.

TotalProfit = SUMX(Sales, Sales[Quantity] * Sales[Unit Price] - Sales[Quantity] * Sales[Unit Cost])

Then calculate a ranking measure for your products based on total profit.

ProductRank = RANKX(ALL(Sales[Product]), [TotalProfit], , DESC, Dense)

This measure will correctly assign a rank to each individual product based on the total profit.

Now, you can create a calculated table where you only include the top 5 products. Here's a table expression for this:

Top5Products = 
FILTER(
    ALL(Sales[Product]),
    [ProductRank] <= 5
)

Now, this will create a new table with just the top 5 products in it, as per the total profit.

Lastly create a measure which will calculate total sales for these top 5 products:

Top5ProductSales = 
CALCULATE(
    SUM(Sales[SalesAmount]),
    FILTER(
        Sales,
        Sales[Product] IN Top5Products[Product]
    )
)

Note: the SalesAmount is an assumed column in the sales table that represents the total sales amount. If the naming is different in your data model, please adjust it accordingly.