Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
lalamaladala_2
New Member

Help need Top N ranking with Category and SubCategory need top 10 and bottom 10 overall

We need to find the   top 10 and bottom 10 supplier based on the Payment term bucket and supplier
Initial rank rank the payment bucket Intial rank 2 rank the highest spend in catgory

I need to filter the top and bottom 10 records

lalamaladala_2_0-1668692392911.png

Tried to comine to the two ranking and then use that ranking in  top based on supplier group based on the COnc Rank

issue is when I do this the measure recalc and does not give me top 10.

InitialRanking min =

RANKX (

ALLSELECTED(Payment_Adhoc_Tab2_rolling[Pymt_map.Pymt_N0],Payment_Adhoc_Tab2_rolling[All.Supplier_Group],Payment_Adhoc_Tab2_rolling[All.BA],Payment_Adhoc_Tab2_rolling[All.PL]),
 
 

CALCULATE (min (Payment_Adhoc_Tab2_rolling[Pymt_map.Pymt_N0] ) ), ,

ASC,

Dense
)




InitialRanking 2 =

RANKX (
FILTER (
ALL (
'Payment_Adhoc_Tab2_rolling'[Pymt_map.Pymt_N0],
'Payment_Adhoc_Tab2_rolling'[All.Supplier_Group]
),
'Payment_Adhoc_Tab2_rolling'[Pymt_map.Pymt_N0]
= MAX ( 'Payment_Adhoc_Tab2_rolling'[Pymt_map.Pymt_N0])
),
CALCULATE (
SUM (Payment_Adhoc_Tab2_rolling[All.Spend_SEK] )
)
)
 
thank  you for any guidance 

 

2 REPLIES 2
lalamaladala_2
New Member

Thank you for your help attached is an example of our data set we are tring to get the top 10 based on the lowest payment terms and top 10 ISsue is filter the 10 when it overlaps payment terms

 

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nVbbjts2EP0VYp9TgvfLIy3RNte6QZJ34wZ5SNsAzVOKzf8DHQ7lNptm6d0ChjwcaQ7FM2dm9OHDXWgfwtDElnRhF7vHcX5/96444T/24ZchgNF++fYnaT49fQaba7hoxrgVlDnN7z6+ey3M/tNvT19+fwYkvfHOUC6seAvQ169/kOnp819XGOecoMpxztX/gPl2hRGcKeYklcoXmDXmOLy+TIZR2nlJvdJb1HlZ00DOc1yPYUCE7KnuzDXXwlCtXDnA7twOqTmRZhwe4gxwB3gInS/ywDKdTCpjLHWmwDRhaUIbyTLNALHAE+ipYgjHhbRUG68LxjisaY3NkRzm8TxljOy5kVjBvZfCU2tcQZlz4FwlQVtunKPOm7JzG5d0GJ5zgL4qirFMOeDSW7WhPMwpLhgLVuW98+k5JJNqx53cohPsvaR9AjHtx9BnmOyqKkI6DT8GOBJBYr/D53fVF/dWw8aKmq0WYg87j0WxYFV3zMwJI6m6GfqTXBnNpNeKqkL7PqS5A+GSNLQg3I089N4gTyhpNRPUM/EmpO+ZwCRIYY01lLGShDQtZArNKRyKCHBd5VJKI22WMSsN6p40IN8wtHAP7Sqb3jjvnKea6dvRPyGUQyeCbHDK7Svif2xm3HDNuKNcFvXchz6cyD7s5tSENWFa0VdFsdZ6qRh11iDKKeY0rHHehyb3JFxXObTGaJCU57Zw2IVTJKUm4S6u6j2NGeGlo3JrJH2YdyEnHo06BcC8olkDhYI+NgH6YSCHsY+/ZojsuJUFobTRknJjXwvy43uADL1nVKhNRlBMqU0YhGZ9mkhmOdQCt/4aPF8eY1iPcb72EvTVleyUcEpRITeUsY3zQKaxu2yljZ6X9IzTETLJNUxrV8pySLv4TzPHRZUEA2KEmUClfUP0dzPVOOb5tRAHqOBjSOQeWJzgP5zXsR9XaKmIBjdvZVWBrqC3bjMC2gI054a8RyS4j45qdfM8mrRwMLE3iPn0GC4YC1b1NEoxmLFQ2dtYm7oAo70ZM/9oV5mUXBrYVlq1RS/Nv3WNnQ09NxiQCuYq5NK+HuQ/pQm1obzjdJty89h1IMo25Szgol4WmikDwrwmdR6X2Keuw2Aw67UNavCWWlkSuESY7y1p0nohB2AwKxp91QMYrUBTikOTLSjHNE1QDmQ5T1N3gW+GfgpDzineqb+QBC6ZpXybNhtE/uQZxm48lKmF3rqupFDQdKl15WTnAb8dQkf6uGLjQ8+N7HrLODdQ7qzU++PSPx+f6KiAlAEKE8gI+A4EjXz8Gw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [All.Supplier_Group1 = _t, All.Supplier_Group = _t, All.BA = _t, All.PL = _t, Pymt_map.Pymt_N0 = _t, Total = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"All.Supplier_Group1", type text}, {"All.Supplier_Group", type text}, {"All.BA", type text}, {"All.PL", type text}, {"Pymt_map.Pymt_N0", Int64.Type}, {"Total", type number}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"All.Supplier_Group1"})
in
#"Removed Columns"

 

calc used 

 

InitialRanking 2 =
VAR ProductRank =
RANKX (
FILTER (
ALLSELECTED( Payment_Adhoc_Tab2_rolling[Pymt_map.Pymt_N0],Payment_Adhoc_Tab2_rolling[All.Supplier_Group]
 
),
'Payment_Adhoc_Tab2_rolling'[Pymt_map.Pymt_N0]
= MAX ( 'Payment_Adhoc_Tab2_rolling'[Pymt_map.Pymt_N0])
),
CALCULATE (
SUM (Payment_Adhoc_Tab2_rolling[Total] )
)
,,ASC,Dense)



RETURN
ProductRank
 
 
InitialRanking min =
VAR ProductRank =
RANKX (

ALLSELECTED(Payment_Adhoc_Tab2_rolling[Pymt_map.Pymt_N0],Payment_Adhoc_Tab2_rolling[All.Supplier_Group],Payment_Adhoc_Tab2_rolling[All.BA],Payment_Adhoc_Tab2_rolling[All.PL]),
 
 

CALCULATE (min (Payment_Adhoc_Tab2_rolling[Pymt_map.Pymt_N0] ) ), ,

ASC,

Dense
)




RETURN
ProductRank
 
RankXDescMeasure2 =
 
VAR ProductRank =
(COUNTROWS(
FILTER(
SUMMARIZE(
FILTER(
ALLSELECTED('Payment_Adhoc_Tab2_rolling'),
'Payment_Adhoc_Tab2_rolling'[Pymt_map.Pymt_N0] = MAX('Payment_Adhoc_Tab2_rolling'[Pymt_map.Pymt_N0])
),
Payment_Adhoc_Tab2_rolling[All.Supplier_Group],
"Distinct",SUM(Payment_Adhoc_Tab2_rolling[Total])
),
Payment_Adhoc_Tab2_rolling[Spend_UNfiltered]>=SUM(Payment_Adhoc_Tab2_rolling[Total])
)
 
))



RETURN
ProductRank
Spend_UNfiltered = sum(Payment_Adhoc_Tab2_rolling[Total])
 
I did not see where I could upload PBI. 
Thnk you for you help
 
 

 

v-rzhou-msft
Community Support
Community Support

Hi @lalamaladala_2 ,

 

If you need to filter the top and bottom 10 records, you can give two ranks, one by asc ,another by desc, then get results from 1-10 based on OR function.

Your calculation is based on your data model, please share a sample file with me and show me a screenshot with the result you need. This will make me easier to find the solution.

 

Best Regards,
Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.