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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Anonymous
Not applicable

Grouping a column with numbers and null values

 

Hi all,

 

I need help to create customized groups or bins that based on a column.

 

The column is last promised date of shipment and actual shipment date.

Which goes from something like -4000 to +4000 ish.

Those outer points are outliers, so im looking to create a group called +100 days and -100 days.

and then smaller groups near the 0 value.

 

how can i achieve this? thanks

kolovez_0-1636362892059.png

i have tried various approaches, but i cant find any help online on this for some reason they dont apply to my problem.

Tried to click group by in the edit query, but it doesn't seem to be the right approach.

kolovez_1-1636363097412.png

 

i also tried to highlight multiple numbers in a simple table, but the "group" option is not available:

kolovez_2-1636363236143.png

 

1 ACCEPTED SOLUTION

Hi @Anonymous ,

 

Do you have defined groups that you require? Like how many total groups you need?

What is the minimum and maximum value in this column "Days between last promised shipment date and actual shipment date" ?

 

You can try to create BINS in Power BI. Consider the scenario where I have got Totals Sales displayed over-time.

Pragati11_0-1636366481221.png

Now I want to create BINS for the SALES value displayed above.

 

What I will do is, under FIELDS PANE, right-click on my SALES column and get following options and select NEW GROUP:

 

Pragati11_1-1636366616956.png

A new window opens where I can create some bins using options based on my requirement:

Pragati11_2-1636366667890.png

Read more about Bons here: https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-grouping-and-binning#using-binning

 

Once you create bins, a new grouping appears as follows under Fields pane below your column:

Pragati11_3-1636366744322.png

Then you can use those BINS under LEGEND area to show the groups by different colors for sales value (in my case):

Pragati11_4-1636366864760.png

 

In a tree-map visual you can achieve something as below:

Pragati11_5-1636367244129.png

My bins are marked in RED in above visual.

 

Let me know if this is what you are looking for.

 

Thanks,

Pragati

 

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

Thanks @Pragati11 .

I did however end up doing a SQL case statement for grouping because Power BI felt very rigid in this scenario, where I don't want the bin sizes to be equal in size and the naming of bins was not customizable. 

 

,CASE
when DATEDIFF(DAY, MAX(RDL.[New Shipment Date]),SSL.[Shipment Date])
< -100 then 'less than -100'
when DATEDIFF(DAY, MAX(RDL.[New Shipment Date]),SSL.[Shipment Date])
between -100 and -51 then '-51 to -100'
when DATEDIFF(DAY, MAX(RDL.[New Shipment Date]),SSL.[Shipment Date])
between -50 and -11 then '-11 to -50'
when DATEDIFF(DAY, MAX(RDL.[New Shipment Date]),SSL.[Shipment Date])
between -10 and -5 then '-5 to -10'
when DATEDIFF(DAY, MAX(RDL.[New Shipment Date]),SSL.[Shipment Date])
between -4 and -1 then '-1 to -4'
when DATEDIFF(DAY, MAX(RDL.[New Shipment Date]),SSL.[Shipment Date])
= 0 then '0'
when DATEDIFF(DAY, MAX(RDL.[New Shipment Date]),SSL.[Shipment Date])
between 1 and 4 then '1 to 4'
when DATEDIFF(DAY, MAX(RDL.[New Shipment Date]),SSL.[Shipment Date])
between 5 and 10 then '5 to 10'
when DATEDIFF(DAY, MAX(RDL.[New Shipment Date]),SSL.[Shipment Date])
between 11 and 50 then '11 to 50'
when DATEDIFF(DAY, MAX(RDL.[New Shipment Date]),SSL.[Shipment Date])
between 51 and 100 then '51 to 100'
when DATEDIFF(DAY, MAX(RDL.[New Shipment Date]),SSL.[Shipment Date])
> 100 then 'more than 100'
Else 'Null' end as 'Group days between last promised shipment date and actual shipment date'

Pragati11
Super User
Super User

Hi @Anonymous ,

 

Can you kindly share some sample data here as I am not clear on what data you have, what columns you are talking about here?

Follow this link to see how you can add details to your query:

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

 

Thanks,

Pragati

 

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

Anonymous
Not applicable

Hi @Pragati11 

 

The data table is quite straighforward.

There are 3 columns

1. Last promised shipment date

2. Actual shipment date

3. Days between last promised shipment date and actual shipment date.

 

And i just need to group the last column. 

 

kolovez_0-1636364275497.png

 

If i dont do anything with the data i can show the distribution and see that 670.000 orders were delivered same day as promised, but i would like to group those numbers, so i say everything between +1 to 5 days. and -1 to -5 days etc.

kolovez_1-1636364632957.png

 

Hi @Anonymous ,

 

Do you have defined groups that you require? Like how many total groups you need?

What is the minimum and maximum value in this column "Days between last promised shipment date and actual shipment date" ?

 

You can try to create BINS in Power BI. Consider the scenario where I have got Totals Sales displayed over-time.

Pragati11_0-1636366481221.png

Now I want to create BINS for the SALES value displayed above.

 

What I will do is, under FIELDS PANE, right-click on my SALES column and get following options and select NEW GROUP:

 

Pragati11_1-1636366616956.png

A new window opens where I can create some bins using options based on my requirement:

Pragati11_2-1636366667890.png

Read more about Bons here: https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-grouping-and-binning#using-binning

 

Once you create bins, a new grouping appears as follows under Fields pane below your column:

Pragati11_3-1636366744322.png

Then you can use those BINS under LEGEND area to show the groups by different colors for sales value (in my case):

Pragati11_4-1636366864760.png

 

In a tree-map visual you can achieve something as below:

Pragati11_5-1636367244129.png

My bins are marked in RED in above visual.

 

Let me know if this is what you are looking for.

 

Thanks,

Pragati

 

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors