topic Re: How Can I Sort My Power BI Matrix by Custom Rank(Top 5, Other, Special Products) for the Latest MAT? in DAX Commands and Tips

How Can I Sort My Power BI Matrix by Custom Rank(Top 5, Other, Special Products) for the Latest MAT?

Sarah_T — Fri, 31 Oct 2025 13:56:05 GMT

Hi everyone,

I’m working on a Power BI project aimed at identifying the number of prescriptions for products on the market. The prescription information (i.e., the number of prescriptions made) is stored in the Prescription Product table, which also contains the time period the data refers to.

I’m also using a Calendar table, linked to the Prescription Product table, to manage time-based filters used in the dashboard.

I’ve already created a measure (which I’ll paste later) that allows me to identify, for the latest MAT available (i.e., the sum of the last 4 quarters), the Top 5 products of a selected market (filtered by slicer).
Additionally, I need to include products that are not in the Top 5 but belong to one of the two “special” companies I’ve defined.
All other products in the selected market, which are neither in the Top 5 nor from the special companies, should be grouped under a single category called “Other.”

This measure is already working (its name is Top5 + Others + Special Product Range Quarter (Fixed Last 4Q)).

However, I’m struggling with one last thing:
I need to sort this measure in a matrix visualization according to the following logic:

First, show the Top 5 products (ranked from 1 to 5 based on the latest MAT value — the highest value should be rank 1);
Then show the “Other” category (rank = 6);
Finally, if present, show the special company products (rank > 6).

The ranking should be calculated only for the latest MAT available (the sum of the last 4 quarters) and should only appear for quarters where data actually exists, meaning it should not appear for quarters beyond the latest available date.

Could anyone kindly help me figure out how to handle this custom sorting logic?

Thanks a lot in advance! 🙏

Top5 + Others + Special Product Range Quarter (Fixed Last 4Q) =

VAR Top_N = 5

VAR CurrentMarket = SELECTEDVALUE('IMF_MKS'[NOME MKS])

VAR CurrentProductRange = SELECTEDVALUE('IMF_MKS'[Product Range])

-- 🔹 1. Data massima effettiva con dati reali (NON MODIFICARE)

VAR MaxDate =

CALCULATE(

MAX('Calendar'[Date]),

FILTER(

ALL('Calendar'),

CALCULATE(NOT ISBLANK([QTR_PrescrProduct]))

)

-- 🔹 2. Inizio finestra: ultimi 4 quarter effettivi (NON MODIFICARE)

VAR StartDate =

CALCULATE(

MIN('Calendar'[Date]),

DATESINPERIOD('Calendar'[Date], MaxDate, -4, QUARTER)

)

-- 🔹 3. Elenco aziende speciali

VAR SpecialManufacturers = { "AAA", "BBB" }

-- 🔹 4. Tabella base dei Product Range nel market corrente

VAR ProductTableBase =

FILTER(

ALL('IMF_MKS'),

'IMF_MKS'[NOME MKS] = CurrentMarket &&

'IMF_MKS'[Product Range] <> "Other"

)

-- 🔹 5. Tabella aggregata per Product Range

VAR ProductTable =

SUMMARIZE(

ProductTableBase,

'IMF_MKS'[Product Range],

"Last4Q_Total",

CALCULATE(

[QTR_PrescrProduct],

REMOVEFILTERS('Calendar'),

KEEPFILTERS(

'Calendar'[Date] >= StartDate &&

'Calendar'[Date] <= MaxDate

)

"HasSpecialManufacturer",

IF(

CALCULATE(

COUNTROWS(

FILTER(

ALL('IMF_MKS'),

'IMF_MKS'[Product Range] = MAX('IMF_MKS'[Product Range]) &&

'IMF_MKS'[MANUFACTERER] IN SpecialManufacturers

)

) > 0,

)

-- 🔹 6. Ranking fisso basato su Last4Q_Total

VAR RankedTable =

ADDCOLUMNS(

ProductTable,

"Rank", RANKX(ProductTable, [Last4Q_Total], , DESC, DENSE)

)

-- 🔹 7. Recupero Rank e flag del Product Range corrente

VAR CurrentInfo =

FILTER(RankedTable, [Product Range] = CurrentProductRange)

VAR CurrentRank =

MAXX(CurrentInfo, [Rank])

VAR IsSpecialProductRange =

MAXX(CurrentInfo, [HasSpecialManufacturer]) = 1

-- 🔹 8. Valore corrente effettivo (nel periodo visibile)

VAR CurrentValue = [QTR_PrescrProduct]

-- 🔹 9. Somma dei prodotti fuori dal Top N e non speciali

VAR OtherValue =

SUMX(

FILTER(

RankedTable,

[Rank] > Top_N &&

[HasSpecialManufacturer] <> 1 &&

[Product Range] <> "Other"

[Last4Q_Total]

)

-- 🔹 10. Output finale

RETURN

SWITCH(

TRUE(),

-- Riga “Other”: mostra solo se ci sono effettivamente valori > 0

CurrentProductRange = "Other" &&

NOT(ISBLANK([OtherValueOnly])) &&

[OtherValueOnly] <> 0,

[OtherValueOnly],

-- Riga “Other” ma nessun valore: nascondi

CurrentProductRange = "Other",

BLANK(),

-- Prodotti Top N: mostra il valore reale

CurrentRank <= Top_N,

CurrentValue,

-- Prodotti speciali (Manufacturer X o Y), anche se non Top N

IsSpecialProductRange,

CurrentValue,

-- Tutti gli altri fuori Top N e non speciali → aggregati in Other

BLANK()

)

Re: How Can I Sort My Power BI Matrix by Custom Rank(Top 5, Other, Special Products) for the Latest MAT?

Jai-Rathinavel — Sat, 01 Nov 2025 15:55:52 GMT

Hi Sarah_T It would be great if you could provide us a pbix file with sample / dummy data. You can upload the file to a dropbox and share the link with us by replying to this thread.

Thanks,
Jai

Re: How Can I Sort My Power BI Matrix by Custom Rank(Top 5, Other, Special Products) for the Latest MAT?

V-yubandi-msft — Wed, 05 Nov 2025 04:42:34 GMT

Hi Sarah_T ,

As suggested by Jai-Rathinavel , please prepare a small Power BI (.pbix) file or share some sample data along with the expected output. This will help us better understand the scenario and provide a more accurate solution.

Thanks Jai-Rathinavel , for your helpful suggestion.

Best regards,
Yugandhar

Re: How Can I Sort My Power BI Matrix by Custom Rank(Top 5, Other, Special Products) for the Latest MAT?

Sarah_T — Fri, 07 Nov 2025 13:07:30 GMT

Good morning everyone, unfortunately I cannot prepare a PBIX version as an example, but I am including a screenshot of the scenario I am referring to. Specifically, the matrix on the right, with the YTD and MAT metrics, shows the correct sorting I am requesting (sorting by MAT in descending order).

My additional request, which I am currently unable to implement, is to identify 5 fixed products plus all the others grouped together under the category "Other" with their respective sum (fixed based on the sum of the last 4 available quarters). For these 5 top fixed products plus the "Other" category, I need to see the data by quarter for the entire available history.

In this first matrix by quarter, these products must follow the same sorting order as in the matrix on the right. That is, in the example provided, the sorting for the matrix on the left must necessarily be:

HyaloGyn
Ozogin Hydra
Ainara
Santes
Meclon Idra
Other

Re: How Can I Sort My Power BI Matrix by Custom Rank(Top 5, Other, Special Products) for the Latest MAT?

Gryphonius — Fri, 07 Nov 2025 14:37:56 GMT

You should have a separate table with all the descriptions and the "Other" row, if the order is not dynamic but only based on the last available date you can add it to that table and use it to order the descriptions.

Otherwise, add a measure for the rank (=order) to the matrix and order by that, then hide it by resizing the column.

Re: How Can I Sort My Power BI Matrix by Custom Rank(Top 5, Other, Special Products) for the Latest MAT?

Sarah_T — Mon, 10 Nov 2025 08:35:09 GMT

I already have a table where, for each identified market, I have all the corresponding descriptions, with the addition of the "Other" row.
However, I can’t add a rank to this table because, with the next data update, there might be products with significantly higher values, which would therefore change the selection of the top 5 products.

I tried creating a rank measure to order these Top 5 + Other + Special (always based on the sum of the last 4 quarters), but the sorting doesn’t come out correctly when I want to display it in a matrix with the entire history of quarters.

Here’s the formula I identified for the rank:

TopN + Manufacturer + Others Product Range Rank =

VAR Top_N = 5

VAR CurrentMarket = SELECTEDVALUE('IMF_MKS'[NOME MKS])

VAR CurrentProductRange = SELECTEDVALUE('IMF_MKS'[Product Range])

VAR CurrentManufacturer = SELECTEDVALUE('IMF_MKS'[MANUFACTERER])

-- 🔹 Manufacturer sempre visibili

VAR AlwaysVisibleManufacturers = {"AAA", "BBB"}

-- 🔸 Crea tabella con Product Range (non manufacturer) per il mercato

VAR ProductRangeTable =

ADDCOLUMNS(

SUMMARIZE(

FILTER(

ALLSELECTED('IMF_MKS'),

'IMF_MKS'[NOME MKS] = CurrentMarket

'IMF_MKS'[Product Range]

"MAT_Value", CALCULATE([MAT_PrescrProduct])

)

-- 🔸 Filtra solo Product Range con MAT non vuoto

VAR ProductRangeWithMAT = FILTER(ProductRangeTable, NOT(ISBLANK([MAT_Value])))

VAR TotalProductsWithMAT = COUNTROWS(ProductRangeWithMAT)

-- 🔸 Ranking a livello di Product Range (aggregato)

VAR RankedTable =

ADDCOLUMNS(

ProductRangeWithMAT,

"Rank", RANKX(ProductRangeWithMAT, [MAT_Value], , DESC, DENSE)

)

-- 🔸 Valore totale dei prodotti fuori Top N (esclusi manufacturer speciali)

VAR OtherValue =

SUMX(

FILTER(

RankedTable,

[Rank] > Top_N

[MAT_Value]

)

-- 🔸 Rank corrente del Product Range

VAR CurrentRank =

MAXX(

FILTER(RankedTable, [Product Range] = CurrentProductRange),

[Rank]

)

-- 🔸 MAT corrente (serve per escludere product range senza valore)

VAR CurrentMAT =

MAXX(FILTER(ProductRangeTable, [Product Range] = CurrentProductRange), [MAT_Value])

-- 🔸 Manufacturer speciale (visibile anche fuori Top5)

VAR IsAlwaysVisibleManufacturer = CurrentManufacturer IN AlwaysVisibleManufacturers

-- 🔸 Posizione dei manufacturer speciali (dopo Other)

VAR SpecialManufacturerRank =

RANKX(

AlwaysVisibleManufacturers,

CurrentManufacturer,

ASC,

DENSE

)

-- 🔸 LOGICA FINALE DEL RANK

VAR FinalRank =

SWITCH(

TRUE(),

-- 1️⃣ Escludi Product Range senza MAT

//ISBLANK(CurrentMAT), BLANK(),

-- 2️⃣ Se mercato ha ≤ Top_N prodotti, non mostrare "Other"

CurrentProductRange = "Other" && TotalProductsWithMAT <= Top_N, BLANK(),

-- 3️⃣ Se ci sono prodotti extra, assegna a "Other" rank = Top_N + 1

CurrentProductRange = "Other" && TotalProductsWithMAT > Top_N, Top_N + 1,

-- 4️⃣ Top N Product Range

CurrentRank <= Top_N, CurrentRank,

-- 5️⃣ Manufacturer speciali fuori Top N → dopo Other

CurrentRank > Top_N && IsAlwaysVisibleManufacturer, Top_N + 1 + SpecialManufacturerRank,

-- 6️⃣ Manufacturer speciali dentro Top N → mantieni il rank originale

CurrentRank <= Top_N && IsAlwaysVisibleManufacturer, CurrentRank,

-- 7️⃣ Tutti gli altri → non mostrare

TRUE(), BLANK()

)

RETURN

FinalRank

This formula calculates the rank for the MAT, for a matrix where only YTD and MAT are displayed.
What I need, however, is a rank formula that calculates the highest values based on the sum of the last 4 quarters, but to be displayed in a matrix that contains the full history of quarters.

Does anyone have any suggestions?

Re: How Can I Sort My Power BI Matrix by Custom Rank(Top 5, Other, Special Products) for the Latest MAT?

Gryphonius — Mon, 10 Nov 2025 09:04:31 GMT

I can't see in your formula where you are filtering for "the last 4 quarters", you should add the filter appropriately.
It would help if you provided a sample of your report, especially if you want to needlessly complicate the DAX.

Re: How Can I Sort My Power BI Matrix by Custom Rank(Top 5, Other, Special Products) for the Latest MAT?

Sarah_T — Mon, 10 Nov 2025 10:24:47 GMT

Unfortunately, I can’t provide an example of my report, but I can share the formula where I calculate the quarterly data for the top 5 products, based on the sum of the last four quarters.
I’m asking for help with the sorting not because I want to needlessly complicate the DAX formula, but because it’s a request coming from other people.

Top5 + Others + Special Product Range Quarter (Fixed Last 4Q) =

VAR Top_N = 5

VAR CurrentMarket = SELECTEDVALUE('IMF_MKS'[NOME MKS])

VAR CurrentProductRange = SELECTEDVALUE('IMF_MKS'[Product Range])

-- 🔹 1. Data massima effettiva con dati reali (NON MODIFICARE)

VAR MaxDate =

CALCULATE(

MAX('Calendar'[Date]),

FILTER(

ALL('Calendar'),

CALCULATE(NOT ISBLANK([QTR_PrescrProduct]))

)

-- 🔹 2. Inizio finestra: ultimi 4 quarter effettivi (NON MODIFICARE)

VAR StartDate =

CALCULATE(

MIN('Calendar'[Date]),

DATESINPERIOD('Calendar'[Date], MaxDate, -4, QUARTER)

)

-- 🔹 3. Elenco aziende speciali

VAR SpecialManufacturers = { "AAA", "BBB" }

-- 🔹 4. Tabella base dei Product Range nel market corrente

VAR ProductTableBase =

FILTER(

ALL('IMF_MKS'),

'IMF_MKS'[NOME MKS] = CurrentMarket &&

'IMF_MKS'[Product Range] <> "Other"

)

-- 🔹 5. Tabella aggregata per Product Range

VAR ProductTable =

SUMMARIZE(

ProductTableBase,

'IMF_MKS'[Product Range],

"Last4Q_Total",

CALCULATE(

[QTR_PrescrProduct],

REMOVEFILTERS('Calendar'),

KEEPFILTERS(

'Calendar'[Date] >= StartDate &&

'Calendar'[Date] <= MaxDate

)

"HasSpecialManufacturer",

IF(

CALCULATE(

COUNTROWS(

FILTER(

ALL('IMF_MKS'),

'IMF_MKS'[Product Range] = MAX('IMF_MKS'[Product Range]) &&

'IMF_MKS'[MANUFACTERER] IN SpecialManufacturers

)

) > 0,

)

-- 🔹 6. Ranking fisso basato su Last4Q_Total

VAR RankedTable =

ADDCOLUMNS(

ProductTable,

"Rank", RANKX(ProductTable, [Last4Q_Total], , DESC, DENSE)

)

-- 🔹 7. Recupero Rank e flag del Product Range corrente

VAR CurrentInfo =

FILTER(RankedTable, [Product Range] = CurrentProductRange)

VAR CurrentRank =

MAXX(CurrentInfo, [Rank])

VAR IsSpecialProductRange =

MAXX(CurrentInfo, [HasSpecialManufacturer]) = 1

-- 🔹 8. Valore corrente effettivo (nel periodo visibile)

VAR CurrentValue = [QTR_PrescrProduct]

-- 🔹 9. Somma dei prodotti fuori dal Top N e non speciali

VAR OtherValue =

SUMX(

FILTER(

RankedTable,

[Rank] > Top_N &&

[HasSpecialManufacturer] <> 1 &&

[Product Range] <> "Other"

[Last4Q_Total]

)

-- 🔹 10. Output finale

RETURN

SWITCH(

TRUE(),

-- Riga “Other”: mostra solo se ci sono effettivamente valori > 0

CurrentProductRange = "Other" &&

NOT(ISBLANK([OtherValueOnly])) &&

[OtherValueOnly] <> 0,

[OtherValueOnly],

-- Riga “Other” ma nessun valore: nascondi

CurrentProductRange = "Other",

BLANK(),

-- Prodotti Top N: mostra il valore reale

CurrentRank <= Top_N,

CurrentValue,

-- Prodotti speciali (Manufacturer X o Y), anche se non Top N

IsSpecialProductRange,

CurrentValue,

-- Tutti gli altri fuori Top N e non speciali → aggregati in Other

BLANK()

)

Re: How Can I Sort My Power BI Matrix by Custom Rank(Top 5, Other, Special Products) for the Latest MAT?

V-yubandi-msft — Tue, 11 Nov 2025 11:58:35 GMT

Hi Sarah_T ,

Thank you for sharing the screenshot and addiotnal details. The matrix is currently re ranking products for each quarter, but the order you’re expecting is based on the latest 4 quarters, as shown in your MAT view. To get the right order, you’ll need to calculate the ranking once using the last 4 quarter period and use that as the sort key in the matrix. You can then hide the rank field so it doesn’t appear in the visual.

Power BI doesn’t automatically keep this custom sort order, so you’ll need a rank field to maintain it. With the rank applied, the Top 5 and Other grouping will stay in the same order across all historical quarters, just like in the MAT matrix on the right.

Thanks for staying engaged and providing the extra context.

Re: How Can I Sort My Power BI Matrix by Custom Rank(Top 5, Other, Special Products) for the Latest MAT?

V-yubandi-msft — Wed, 19 Nov 2025 07:37:09 GMT

Hi Sarah_T ,
Could you let us know if your issue has been resolved or if you are still experiencing difficulties? Your feedback is valuable to the community and can help others facing similar problems.

Re: How Can I Sort My Power BI Matrix by Custom Rank(Top 5, Other, Special Products) for the Latest MAT?

V-yubandi-msft — Wed, 26 Nov 2025 06:42:43 GMT

Hi Sarah_T ,

Are things clear now, or are you still facing any issues? Please let us know if you need any additional information, happy to help.

Re: How Can I Sort My Power BI Matrix by Custom Rank(Top 5, Other, Special Products) for the Latest MAT?

Sarah_T — Wed, 26 Nov 2025 07:14:32 GMT

Hi, No, unfortunately I haven’t been able to sort out my problem yet.

Re: How Can I Sort My Power BI Matrix by Custom Rank(Top 5, Other, Special Products) for the Latest MAT?

V-yubandi-msft — Thu, 27 Nov 2025 10:15:42 GMT

Hi Sarah_T ,

Thank you for your patience and for explaining your requirements clearly.

I was able to reproduce your scenario using sample data and can confirm that it’s possible to maintain a fixed sorting order Top 5 based on the last 4 quarter totals, plus Other while showing all historical quarters in the matrix without re-ranking for each period.

FYI:

I’ve attached a PBIX file for reference, which includes dummy data, the ranking logic for the latest 4 quarters, and the correct matrix setup showing the full quarter history with fixed sorting.

If you need any changes or further adjustments, please feel free to let me know. I’ll be happy to help.

Thanks again for your time and input. I hope this is helpful.

Re: How Can I Sort My Power BI Matrix by Custom Rank(Top 5, Other, Special Products) for the Latest MAT?

Sarah_T — Thu, 27 Nov 2025 10:55:31 GMT

Hi V-yubandi-msftm,

great work! However, I think I didn’t explain myself clearly. Starting from your example: in the PBIX file you shared, you assumed that the 5 products to be shown explicitly were fixed and already known.

But my issue is actually one step earlier, and my request is based on two specific needs:

Dynamically identify the Top 5 products based on the sum of the latest 4 available quarters, and consequently define the “Other” category. (Right now, the Top 5 happen to be the same ones you highlighted in your example, but in two months—when new data and new quarters are loaded—those Top 5 products could be completely different!)
Then calculate the ranking based on these 5 products, which will change over time as new quarterly data is added.

I hope this explanation is clearer.
Do you think you could help me find a solution for this as well?

Re: How Can I Sort My Power BI Matrix by Custom Rank(Top 5, Other, Special Products) for the Latest MAT?

Ahmed-Elfeel — Fri, 28 Nov 2025 04:04:02 GMT

HI Sarah_T,

So If I understood right you need to implement a custom sorting logic in Power BI that follows this specific order:

Top 5 products by MAT value (descending)
then Other category
then any special products

So to do this here is several Approaches you could try

First Approach:

Create a Ranking Measure for Custom Sorting:

Product Custom Rank = VAR Top_N = 5 VAR CurrentMarket = SELECTEDVALUE('IMF_MKS'[NOME MKS]) VAR CurrentProductRange = SELECTEDVALUE('IMF_MKS'[Product Range]) -- Get the latest MAT period (same logic as your existing measure) VAR MaxDate = CALCULATE( MAX('Calendar'[Date]), FILTER( ALL('Calendar'), CALCULATE(NOT ISBLANK([QTR_PrescrProduct])) ) ) VAR StartDate = CALCULATE( MIN('Calendar'[Date]), DATESINPERIOD('Calendar'[Date], MaxDate, -4, QUARTER) ) -- Calculate MAT values for ranking VAR ProductMATValues = SUMMARIZE( FILTER( ALL('IMF_MKS'), 'IMF_MKS'[NOME MKS] = CurrentMarket && 'IMF_MKS'[Product Range] <> "Other" ), 'IMF_MKS'[Product Range], "MAT_Value", CALCULATE( [QTR_PrescrProduct], REMOVEFILTERS('Calendar'), 'Calendar'[Date] >= StartDate && 'Calendar'[Date] <= MaxDate ) ) -- Rank products by MAT value VAR RankedProducts = ADDCOLUMNS( ProductMATValues, "Rank", RANKX(ProductMATValues, [MAT_Value], , DESC, DENSE) ) -- Get rank for current product VAR CurrentProductRank = MAXX( FILTER(RankedProducts, [Product Range] = CurrentProductRange), [Rank] ) -- Define special manufacturers VAR SpecialManufacturers = { "AAA", "BBB" } VAR IsSpecialProduct = CALCULATE( COUNTROWS( FILTER( ALL('IMF_MKS'), 'IMF_MKS'[Product Range] = CurrentProductRange && 'IMF_MKS'[MANUFACTERER] IN SpecialManufacturers ) ) ) > 0 -- Return custom rank based on your logic RETURN SWITCH( TRUE(), CurrentProductRange = "Other", Top_N + 1, -- Other always gets rank 6 CurrentProductRank <= Top_N, CurrentProductRank, -- Top 5 keep their rank IsSpecialProduct, Top_N + 2 + CurrentProductRank, -- Special products after Other BLANK() -- Hide non-top, non-special products (they go to Other) )

Second Approach: Create a Sorting Table

For more reliable sorting create a calculated table:

Product Sorting = VAR Top_N = 5 VAR CurrentMarket = "Your Market Name" -- or make this dynamic -- Get all products and their MAT values VAR ProductMATValues = SUMMARIZE( FILTER( ALL('IMF_MKS'), 'IMF_MKS'[NOME MKS] = CurrentMarket && 'IMF_MKS'[Product Range] <> "Other" ), 'IMF_MKS'[Product Range], "MAT_Value", CALCULATE( [QTR_PrescrProduct], -- Use your existing MAT date logic here ) ) -- Rank products VAR RankedProducts = ADDCOLUMNS( ProductMATValues, "CustomRank", VAR ProductRank = RANKX(ProductMATValues, [MAT_Value], , DESC, DENSE) VAR IsSpecial = -- Your special product logic RETURN SWITCH( TRUE(), ProductRank <= Top_N, ProductRank, IsSpecial, Top_N + 2 + ProductRank, Top_N + 1 -- Other ) ) RETURN RankedProducts

Third Approach: Enhanced Version with Dynamic Market Selection (More robust for changing filters)

Builds on First Approach but handles dynamic market selection

Dynamic Product Rank = VAR Top_N = 5 VAR CurrentMarket = SELECTEDVALUE('IMF_MKS'[NOME MKS]) VAR CurrentProductRange = SELECTEDVALUE('IMF_MKS'[Product Range]) IF(ISBLANK(CurrentMarket) || ISBLANK(CurrentProductRange), RETURN(BLANK())) -- Your existing MAT calculation logic here VAR MaxDate = ... VAR StartDate = ... VAR ProductMATValues = SUMMARIZE( FILTER( ALL('IMF_MKS'), 'IMF_MKS'[NOME MKS] = CurrentMarket ), 'IMF_MKS'[Product Range], "MAT_Value", [Your MAT Measure] -- Reference your existing MAT measure ) -- Rest of the ranking logic...

if this post helps, then I would appreciate a thumbs up and mark it as the solution to help the other members find it more quickly.

Re: How Can I Sort My Power BI Matrix by Custom Rank(Top 5, Other, Special Products) for the Latest MAT?

V-yubandi-msft — Sat, 29 Nov 2025 03:11:05 GMT

Hi Sarah_T ,

Thank you for the detailed clarification it really helped refine the solution.

I’ve now tested the scenario again using your logic, and I can confirm the following.

1. The Top 5 products are calculated dynamically based on the rolling sum of the most recent 4 quarters (Last4Q), not on fixed values.
2. When new quarterly data is loaded in the future, the Top 5 list and the Other group will automatically update.
3. This same Top 5 order is then applied consistently across the full quarter history, without re ranking each quarter separately.
4. The sorting is driven by a Rank measure (based on the Last4Q calculation), not by manual sorting on the Total column so it remains correct even when the data changes.

Here’s an example of the dynamic output.

I’ve attached the PBIX file again. Please have a look and let me know if any changes are needed or if I have misunderstood any part of the scenario.

Thank you.

Re: How Can I Sort My Power BI Matrix by Custom Rank(Top 5, Other, Special Products) for the Latest MAT?

Sarah_T — Mon, 01 Dec 2025 08:42:07 GMT

Hi V-yubandi-msft ,

The logic for identifying the top 5 based on the sum of the last four available quarters works correctly, as does, apparently, the sorting logic. What I don’t understand is why, in my final visualization matrix, I don’t see any data for the Other category. In other words, the Other category is not displayed at all in the final matrix, as is also shown in the screenshot you provided.

Thank you again for your help

Re: How Can I Sort My Power BI Matrix by Custom Rank(Top 5, Other, Special Products) for the Latest MAT?

V-yubandi-msft — Fri, 05 Dec 2025 06:44:20 GMT

Hi Sarah_T ,

Thanks again for the clarification I rebuilt the entire scenario from scratch using a clean sample dataset to validate each step of the logic and confirm the outcome.

Everything is now working as expected, including the dynamic Top 5 logic, stable ranking, and the consistent display of the Other category.

Please take a moment to review the results and let me know if you’d like any adjustments or if anything behaves differently in your actual dataset.

Thank you.

Re: How Can I Sort My Power BI Matrix by Custom Rank(Top 5, Other, Special Products) for the Latest MAT?

V-yubandi-msft — Mon, 08 Dec 2025 13:11:45 GMT

Hi Sarah_T ,
Is everything clear now, or do you need any additional information? If so, please let us know.

Re: How Can I Sort My Power BI Matrix by Custom Rank(Top 5, Other, Special Products) for the Latest MAT?

Sarah_T — Tue, 09 Dec 2025 07:44:15 GMT

Hi V-yubandi-msft ,

no, I still haven’t been able to solve my problem, and unfortunately your solution was only partially helpful (the absolute value is correct, but the issue lies in the rank calculation), because:

My “Others” category is not a fixed product. Based on the available data, it includes everything that, according to the sum of the last 4 quarters, falls outside the top 5 products.
Even if I fix this issue (which, however, is still not fully correct for all markets that can be selected from the filter), another problem arises with the rank, because it assigns a rank value even for Q3 and Q4 2025, for which I actually don’t have any data yet.
It seems that, due to the rank formula, there is no way to solve this problem: either I get the correct grouping for “Others” but also get ranks for Q3 and Q4 2025, or the last two quarters don’t show any rank, but the grouping for “Others” is still not correct.