Re: How Can I Sort My Power BI Matrix by Custom Ra...

Sarah_T · ‎10-31-2025

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,

1,

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()

)

Ahmed-Elfeel

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.

V-yubandi-msft

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.

Sarah_T

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

V-yubandi-msft

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.

Sarah_T

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?

V-yubandi-msft · ‎11-18-2025

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.

V-yubandi-msft · ‎11-11-2025

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.

Gryphonius · ‎11-07-2025

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.

Sarah_T · ‎11-10-2025

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?

Gryphonius · ‎11-10-2025

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.

Sarah_T · ‎11-10-2025

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,

1,

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()

)

V-yubandi-msft · ‎11-04-2025

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

Jai-Rathinavel · ‎11-01-2025

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

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Sarah_T · ‎11-07-2025

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: