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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

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
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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