Hi @Ania26 ,
To achieve this dynamic Top 5 matrix in DAX, the best approach is to create two disconnected tables—one for the rows and one for the columns—and then write a comprehensive measure to handle the logic. This keeps your model clean and your calculations powerful. Assume your main table is named Sales.
First, you'll need to create these two tables using the New Table option under the Modeling tab. These tables will provide the labels for your matrix axes and must not have a relationship with your main Sales table.
Create the table for your Origin Markets:
Origin Market Group =
UNION(
VALUES('Sales'[Origin Market]),
ROW("Origin Market", "Others")
)
Then, create the corresponding table for your Destination Markets:
Destination Market Group =
UNION(
VALUES('Sales'[Destination Market]),
ROW("Destination Market", "Others")
)
With the tables for your axes ready, you can now create the DAX measures. It's good practice to have a simple base measure for the sum, which the main measure will use.
Total Value = SUM('Sales'[Value])
Now, here is the primary measure that performs all the heavy lifting. It identifies the top markets based on the filter context (like a selected year), compares them to the selections on the matrix axes, and calculates the appropriate value for each cell, including the "Others" aggregations.
Matrix Value =
VAR TopNValue = 5
// Determine Top 5 Origin Markets based on the total value in the current filter context
VAR TopOriginMarkets =
TOPN(
TopNValue,
CALCULATETABLE(
VALUES('Sales'[Origin Market]),
ALL('Sales'[Origin Market])
),
[Total Value],
DESC
)
// Determine Top 5 Destination Markets
VAR TopDestinationMarkets =
TOPN(
TopNValue,
CALCULATETABLE(
VALUES('Sales'[Destination Market]),
ALL('Sales'[Destination Market])
),
[Total Value],
DESC
)
// Get the market selected on the matrix axis from our disconnected tables
VAR SelectedOrigin = SELECTEDVALUE('Origin Market Group'[Origin Market])
VAR SelectedDestination = SELECTEDVALUE('Destination Market Group'[Destination Market])
// Calculate the result based on whether the selected market is in the Top 5 or is "Others"
VAR Result =
SWITCH(
TRUE(),
// Case 1: A Top Origin intersecting with a Top Destination
SelectedOrigin <> "Others" && SelectedDestination <> "Others",
CALCULATE(
[Total Value],
'Sales'[Origin Market] = SelectedOrigin,
'Sales'[Destination Market] = SelectedDestination
),
// Case 2: A Top Origin intersecting with "Others" Destination
SelectedOrigin <> "Others" && SelectedDestination = "Others",
CALCULATE(
[Total Value],
'Sales'[Origin Market] = SelectedOrigin,
NOT('Sales'[Destination Market] IN TopDestinationMarkets)
),
// Case 3: "Others" Origin intersecting with a Top Destination
SelectedOrigin = "Others" && SelectedDestination <> "Others",
CALCULATE(
[Total Value],
NOT('Sales'[Origin Market] IN TopOriginMarkets),
'Sales'[Destination Market] = SelectedDestination
),
// Case 4: "Others" Origin intersecting with "Others" Destination
SelectedOrigin = "Others" && SelectedDestination = "Others",
CALCULATE(
[Total Value],
NOT('Sales'[Origin Market] IN TopOriginMarkets),
NOT('Sales'[Destination Market] IN TopDestinationMarkets)
)
)
RETURN
// Ensure that a value only appears if the selected market is actually in the Top list for that category
IF(
(SelectedOrigin <> "Others" && NOT(SelectedOrigin IN TopOriginMarkets)) ||
(SelectedDestination <> "Others" && NOT(SelectedDestination IN TopDestinationMarkets)),
BLANK(),
Result
)
To build the final report, add a Matrix visual to your canvas. Drag the Origin Market field from your Origin Market Group table into the Rows field well. Next, drag the Destination Market field from the Destination Market Group table into the Columns field well. Finally, place your new [Matrix Value] measure into the Values field. Add a slicer for the [Year] field from your Sales table, and you're all set! Your visual will now work exactly as requested.
Best regards,
