Creating a Summary Table with Ranking in DAX

To achieve the requested result, we would need nested DAX functions. Here is a breakdown of the logic:

1. SUMMARIZE FUNCTION: This will be used to create a summary table, where each row consists of a unique combination of region and product category, along with the total sales for each combination.

2. RANKX FUNCTION: It should be used to rank the total sales for each product category within each region.

3. TOPN FUNCTION: It will retrieve the top 5 product categories (based on the RANKX rankings) for each region.

Here's how you might put it together in a DAX query:

EVALUATE
ADDCOLUMNS (
    SUMMARIZE (
        'Sales', 
        'Sales'[Region], 
        'Sales'[ProductCategory], 
        "TotalSales", SUM ( 'Sales'[SalesAmount] )
    ), 
    "Rank", 
    RANKX (
        ALL ( 'Sales'[ProductCategory] ), 
        [TotalSales], 
        , 
        DESC, 
        DENSE
    )
)
ORDER BY 
[Region], 
[Rank]

This code creates a summarized table including each unique combination of Region and ProductCategory, with the total sales for each. The RANKX function is then used to rank these total sales amounts within each region, and finally the TOPN function selects the top 5 product categories for each region based on these rankings.

It is also possible to define measures for TotalSales and Rank, and then use these measures in a visual in Power BI. This can make the code cleaner and more reusable.

Please note that RANKX function evaluates an expression for each row of table, and returns the rank of each value in a column, therefore we are ranking total sales within each ProductCategory.

Also, one things to be mindful of is the DAX context transition which is occuring in our RANKX function. 'ALL (Sales[ProductCategory])' creates a row context and since we are using [TotalSales] measure inside RANKX it forces the context transition from row to filter. The 'ALL' function in the example above is used to remove any active filters on the ProductCategory field. This is necessary so that the RANKX function properly ranks all product categories within each region, not just those that are filtered.

Remember, the way DAX functions work can often depend on the existing filters and row context. It's always important to carefully consider the current context when writing and debugging DAX.

If you want to deep dive in to DAX formulas and expressions, consider exploring courses at Enterprise DNA Platform which cover these topics extensively.