Creating groups based on numbers

[aksl]

I'd like to create groups based on numbers (i.e. if column X is 0-9, returns "0-9 members", if column S is 10-24, returns "10-24 members", etc.).  If I use the grouping or list function, it won't let me use <=> and lists the category as every number within that group, makes any visualization outrageously messy, and won't accommodate updated data within the number spans unless it is one of the exact numbers that has already been used.  I can only seem to use one IF function in a column - is that correct?  Can anyone suggest a work-around?

Thank you!

[Chihiro · Accepted solution]

Select the table/query in question. Hit "Edit Queries".

In there, you have option to add blank query. Do so and go to Advanced Editor.

Paste in "M" as below (you can adjust range as needed or add more arugments).

(vals) =>
let ValBucket =
    {
        {(x)=>x<10, "0-9 members"},
        {(x)=>x<25, "10-24 members"},
        {(x)=>x<50, "25-49 members"},
        {(x)=>true, "50+ members"}
    },
    Result = List.First(List.Select(ValBucket, each _{0}(vals))){1}
in
    Result

Once confirmed, rename the query to something that makes sense (Ex. BucketFunc).

Now, still in Query Editor, go back to your table where you want to add custom buckets/bands.

In "Add column" ribbon tool, select "Invoke Custom Function".

Choose a name for the column and select "BucketFunc" you just created. "vals" should be column where the values you want to band belongs.

Result should look like below.

[karlosdsouza]

Its as easy as grouping in pivot table. Refer to below tutorial

https://docs.microsoft.com/en-us/power-bi/desktop-grouping-and-binning

[v-chuncz-msft · Accepted solution]

@aksl,

You could also use DAX to add a calculated column.

Group Name =
SWITCH (
    TRUE (),
    Table1[column X] >= 0
        && Table1[column X] <= 9, "0-9 members",
    Table1[column X] >= 10
        && Table1[column X] <= 24, "10-24 members",
    "other"
)

[Anmolgan]

@v-chuncz-msft  I have some negative values also in my dataset but the switch statment is catching those negative values, and these are by default been thrown into other values, how can I group negative values in 0 to 20 statment??

[Anmolgan]

@v-chuncz-msft Also whenever I try to show this in my matrix field it never works, do you think you can help me create the below:

groupings in matrix table, that initailly I had build in the attached link?

• 0-20 Kl
• 21-49 Kl
• 50-99 Kl
• >100 Kl

https://ifitech-my.sharepoint.com/:u:/g/personal/anmol_ganju_ifi_tech/EaBhGJ3vfFJIluYnxZqST1IBLdzU2Y...

[biswad]

You are a Star * * * * * * * You saved my life. Thanks alot

[Chihiro]

I'd create Custom Bucket/banding function using "M".

You can find detailed example in below link.

https://www.excelguru.ca/blog/2016/02/29/creating-a-banding-function-in-power-query/

While example pertains to PowerQuery in Excel, same principle applies to PowerBI.

[aksl]

Thanks for your response, Chihiro.  I follow the concept, but I still don't see that PowerBI has the functionality to execute this.  Am I missing something?  

I could do it in Excel, but I am trying to create something that is dynamic, so would like to be able to do it in BI if possible.

[Chihiro · Accepted solution]

Select the table/query in question. Hit "Edit Queries".

In there, you have option to add blank query. Do so and go to Advanced Editor.

Paste in "M" as below (you can adjust range as needed or add more arugments).

(vals) =>
let ValBucket =
    {
        {(x)=>x<10, "0-9 members"},
        {(x)=>x<25, "10-24 members"},
        {(x)=>x<50, "25-49 members"},
        {(x)=>true, "50+ members"}
    },
    Result = List.First(List.Select(ValBucket, each _{0}(vals))){1}
in
    Result

Once confirmed, rename the query to something that makes sense (Ex. BucketFunc).

Now, still in Query Editor, go back to your table where you want to add custom buckets/bands.

In "Add column" ribbon tool, select "Invoke Custom Function".

Choose a name for the column and select "BucketFunc" you just created. "vals" should be column where the values you want to band belongs.

Result should look like below.

[anmolkatia]

I am creating a table like no. of accounts and Delay Payments(in no. of days). 

What if I want something like.

Delay Payments(in days)              No. of Accounts

0+                                                 5000

30+                                                3800

60+                                                2700

90+                                                2300

So on... Is it possible in Power Bi

[Anonymous]

Awesome, learned something new today

[sotoc]

I need to do this using only Direct Query mode. My employee counts have ranges that are not eligible for binning. Any help is appreciated. 

Customer	#Employees	Category
A	15	10 to 25
B	10	10 to 25
C	4	0 to 5
D	2	0 to 5
E	2	0 to 5
F	0	0 to 5
G	8	6 to 9

[biswad]

Hi friend, use SWITCH function , it helped me. You can find the solution on my post.

[Gabrielfranco]

Hi mate, 

did u got a solution to this kind categorization? I'm trying to do something like that as well.

[ChihiroK]

Alternate to what biswad wrote, if you are using SQL statement in your Direct Query...

You can use CASE WHEN statement to create the column during query, rather than adding calculated column using DAX.

Or if you have the rights, you could just create View/Stored Proc with the column in SQL side and import as is.