Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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...
Need Help
  • Pragati11's avatar
    Pragati11
    4 years ago

    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.

    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:

     

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

    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:

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

     

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

    My bins are marked in RED in above visual.

     

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

     

    Thanks,

    Pragati

     

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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'