DAX Formula to Rank Top 5 Customers by Sales

Formula

To rank the top 5 customers based on sales, you can use the following DAX formula:

Top5Customers =
TOPN(
    5,
    SUMMARIZE(
        Sales,
        Sales[Customer],
        "TotalSales", SUM(Sales[SalesAmount])
    ),
    [TotalSales],
    DESC
)

Explanation

Breakdown of the Formula Components:

1. TOPN Function:

   • TOPN(5, ...) retrieves the top N rows from a table, here set to 5 for top customers.

2. SUMMARIZE Function:

   • SUMMARIZE(Sales, Sales[Customer], "TotalSales", SUM(Sales[SalesAmount])) creates a virtual table summarizing total sales per customer.
     • Sales: The table containing sales data.
     • Sales[Customer]: The column containing customer names.
     • TotalSales: A calculated column aliasing the sum of sales amounts for each customer.

3. Order By Clause:

   • [TotalSales], DESC sorts the customers based on the total sales in descending order to ensure that the highest sales amounts are ranked first.

Practical Illustration

Scenario

Consider a sales dataset named Sales that includes the following columns:

• Customer: Represents the name of the customer.
• SalesAmount: Represents the total sales made to each customer.

Example Data Table

Result of the Formula

By applying the Top5Customers formula, the resulting table would display:

This table shows the top 5 customers ranked by their total sales amounts.

Conclusion

The provided DAX formula efficiently identifies and ranks the top 5 customers based on their sales figures. It can be used in Power BI reports or dashboards to visualize customer performance, allowing businesses to focus on their most valuable clients. For further enhancement of your DAX skills, consider exploring courses offered on the Enterprise DNA Platform.