As data on what to update was not available until October 2023, to establish a dynamic table in Power BI displaying the top 5 selling products per region, use DAX measures and visual filters. The table will update itself based on whatever filters the user applies.
1. Create Measure for Total Sales
This measure gives the total sales for each given product:
Total Sales = SUM(Sales[Amount])
2. Create a Ranking Measure
To rank products within each region, use the RANKX function:
Product Rank =
RANKX(
FILTER(
Sales,
Sales[Region] = SELECTEDVALUE(Sales[Region]) -- Ensure ranking is per region
),
[Total Sales],
, DESC, DENSE
)
Products are ranked according to sales within each Region.
DENSE relies on assigning consecutive ranks (for example, 1, 2, 3, 3, 4).
3. Apply a Visual-Level Filter.
Add a Table or Matrix visualization with Product Name, Region, and Total Sales.
The visual filter is Applied to Product Rank by Rank ≤ 5.
The table would now simultaneously update depending on the chosen Region or any other filter.
Alternative: Create a Calculated Table for Static Reporting.
If your interest lies in getting a pre-computed table rather than a dynamic visual, then this DAX table should be used:
Top5ProductsPerRegion =
FILTER(
ADDCOLUMNS(
Sales,
"Product Rank",
RANKX(
FILTER(Sales, Sales[Region] = EARLIER(Sales[Region])),
Sales[Total Sales],
, DESC, DENSE
)
),
[Product Rank] <= 5
)
This table stores the top 5 products per region permanently but won’t update dynamically with slicers.
