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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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

Top Solution Authors