How to divide values into 'N' number of groups

[Priyadar_V1]

Hi. I am new to powerbi. I have a column with different value and the range of these number changes. I am trying to catergories the column into 10 scales . This could be done using if statements but I feel it is not the right way to do it.  Example of my data set -(I have divided the data into three scales)

sales	rank
4	1
6	2
3	1
10	3
8	3
5	2

I have done this calculation in excel using the formula (excel - Dividing a column into N equal groups by value - Stack Overflow)

=MAX(ROUNDUP(PERCENTRANK([Range],[OneCellOfTheRange]) *[NumberOfBuckets],0),1)

I am trying to do the same in powerbi.  Thank you in advance

[v-yanjiang-msft · Accepted solution]

Hi @Priyadar_V1 ,

According to your description, here's my solution. Create a calculated column.

Column =
VAR _Rank =
    RANKX ( 'Table', 'Table'[sales],, ASC )
VAR _Range = 3
VAR _Rows =
    COUNTROWS ( 'Table' )
RETURN
    ROUNDUP ( DIVIDE ( _Rank, DIVIDE ( _Rows, _Range ) ), 0 )

Get the correct result.

You can manually modify the variable _Range per your need.

You can also learn about binning in Power BI, which is a way to group based on the value of the column.

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.

[Priyadar_V1]

Thank you so much 🙂