Grouping Of Supplier as per spend

[cyborgandy]

Hi All,

I have below dataset,

I need make a table to have a group of distinct count supplier top 1-2 3-4 and 5-6 as per spend , can some please help how to get this done in power bi

Company Name	Spend
ABC	2000
EFG	3000
HIJ	4000
KLM	5000
NOP	6000
QRS	7000
TUV	8000
WXY	9000
ABC	10000
EFG	11000
HIJ	12000
KLM	13000
NOP	14000
QRS	15000
TUV	16000
WXY	17000

[v-yanjiang-msft · Accepted solution]

Hi @cyborgandy ,

According to your description, here's my solution.

1. Create a calculated column.

Rank =
RANKX ( 'Table', 'Table'[Spend] )

2. For example I want to divide the table into groups of 3 companies, it means there're 3 companies in each group.

Create a calculated column.

Group =
VAR _D =
    DIVIDE ( 'Table'[Rank], 3 )
RETURN
    IF ( _D > INT ( _D ), INT ( _D ) + 1, _D )

 Get the correct result.

You just need to change 3 in the formula to 50(you mentioned) in your sample.

I attach my sample below for your reference.

Best Regards,
Community Support Team _ kalyj

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

[PVVBl00]

In the filters tab for the table you have the option to make a top for the amount you want, in this example I made you a top 5 of Name spend according to the amounts or then you order them from highest to lowest to show them in order, if this is or that you were looking for please mark it as an answer for other users to find it easier, Best regards

[cyborgandy]

Thanks for your response, its dummy value that I have given, I have more that 1600 company with the spend and I need todivide these 1600 into group of 50 companies from highest spend to lowest spend.