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