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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
aksl
Frequent Visitor

Creating groups based on numbers

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!

2 ACCEPTED SOLUTIONS

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.

1.JPG

 

Result should look like below.

2_Result.JPG

View solution in original post

v-chuncz-msft
Community Support
Community Support

@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"
)
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

14 REPLIES 14
karlosdsouza
Helper II
Helper II

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
Community Support
Community Support

@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"
)
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@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??

 

@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...

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

Chihiro
Solution Sage
Solution Sage

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
Frequent Visitor

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.

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.

1.JPG

 

Result should look like below.

2_Result.JPG

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
Not applicable

Awesome, learned something new today

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#EmployeesCategory
A1510 to 25
B1010 to 25
C40 to 5
D20 to 5
E20 to 5
F00 to 5
G86 to 9

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

Hi mate, 

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

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.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.