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.
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.
