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

[lalamaladala_2]

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

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 

[lalamaladala_2]

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]

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.