1

Making Custom Bins With Measures For Discover

So I have a measure that I want to use to make bins. Discover is able to have me create bins by rank, value, category, size, parameter, etc.  Still, is there a way for me to create bins by custom values?

For example, 

lets say I have this grid with dimension name and measure age

Name                 Age

Stacy                  10

John                   20

Mary                   30

Richard              40

.

.

.

and so forth

If I do something like bin by rank with 2 bins, I'll get 

Name                 Bin by Age

Stacy                  10-20

John                   10-20

Mary                   30-40

Richard              30-40

.

.

.

and so forth with 2 bins called "10-20" and "30-40". 

But what I want to do instead is  

Name                 Bin by Age

Stacy                  10 to 20

John                   10 to 20

Mary                   30 to 40

Richard              30 to 40

.

.

.

where it takes all of the ages measures and finds out which ones are <20 and gives them the bin called "10 to 20" (and I can change this custom name and the condition (<20) whenever I want) and does the same thing for >20 and puts it in the "30 to 40" bin. 

And this custom Bin by Age should be a blue chip not an orange chip. 

8 replies

null
    • imbarmarinescubar_pyram
    • 13 days ago
    • Reported - view

    Hi  ,

    You can create a custom column and using the case function split the Age attribute into custom bins.

    For example, I created this custom column:

    case(
    [customerProfile].[Age] < 10, "0 - 10",
    [customerProfile].[Age] < 20, "10 - 20",
    [customerProfile].[Age] < 30, "20 - 30",
    "30+"
    )
    

    Which outputs like this in the grid:

    • Cindy_Tran
    • 12 days ago
    • Reported - view

    Hi  ,

    Is it possible to implement that above but lets say age are considered measures and not a model attribute.  So the custom columns has element blocks but there are only model attribute, numeric and text parameter options and the model attribute only takes hierarchies that are my dimensions (blue chip). I am unable to select any measure (orange chip) in custom columns. 

      • imbarmarinescubar_pyram
      • 12 days ago
      • Reported - view

      The technical answer would be "no" on nesting measures within the expression of a custom column (that's why you can't see any blocks for them).
      But maybe if you share some more details about the model we could find the right approach to build the calculation you need.

      Given names and ages, I would just say to treat the measure as an attribute, since it's a type of property describing the person, that shouldn't be an issue (by the way, it could be both attribute and measure in the model, if needed). 
      Or is this example not based on the actual entities you are dealing with?

      • Cindy_Tran
      • 12 days ago
      • Reported - view

       Yeah the information I was working with is sensitive, so I made a random table for reference. Overall, the format is that the information that I'm working with is a measure (orange chip) and I wanted to make a custom bin (like not using the built in bin by values and such) because I wanted to bin the measure by my own custom condition (like greater than/less than some number) and be able to set that to a custom bin name. 

      I know you're able to do it with model attributes (dimensions) just wondering if the same can be applied to measures. 

      If that is not possible to do in pyramid, that's alright, thanks for letting me know. 

      • imbarmarinescubar_pyram
      • 12 days ago
      • Reported - view

       
      You can create a textual custom measure like this:

      case(
      [measures].[data returns] < 10, "0-10",
      [measures].[data returns] < 20, "10-20",
      [measures].[data returns] < 30, "20-30",
      "+30")

      And assuming you wanted it to be an attribute so that you can filter by it, you could create a measure filter (instead of a slicer).
      Something that would look like this:
      (you can include or exclude each of the 3 measures from the grid, the filter would still work) 

       

      *if you need "single select" for the bins, you can use a text parameter with per-defined values and make it a buttons slicer instead of the free text one.

      *attached this example below, you can import it and point the report to the sample demo model that comes with the pyramid installation. 

    • NPANS
    • 12 days ago
    • Reported - view

     We do this all the time. The binning effectively takes any value column and introduces it as an attribute.  Create a new custom column (or virtual column in the model) and use Imbar's suggestion to create manual bins on any numeric column. This takes each transactional item and puts it into a grouping, like below. You can then slice and dice as normal with this attribute and you're done. 

    If you want to get really clever, parameterize your logic, so the user can change the bin sizes. It complicates things a little, but produces a dynamic bin.

    case(
    [fact].[sales] < 10, "0 - 10",
    [fact].[sales] < 20, "10 - 20",
    [fact].[sales] < 30, "20 - 30",
    "30+"
    )
    
    

    Here is an example on one of the sales samples:

    case(
    [transactions].[Quantity] < 5, "0 - 5",
    [transactions].[Quantity] < 8 && [transactions].[Quantity] >= 5, "5 - 8",
    "8+"
    )
      • Cindy_Tran
      • 12 days ago
      • Reported - view

       I want to make custom columns but using the measure element. See the pictures below. In Formulate Formula (first pic) it allows me to select a measure from the element block but the custom column (second pic) there is no measure. 

      • NPANS
      • 12 days ago
      • Reported - view

       Thats because there is technically no "measure" in custom columns. There are only table columns (otherwise known as model attributes).

      So, if you have a measure called "SALES" in the semantic model, you should be looking for the underlying table column (or model attribute) driving the sales measure. It is usually a column in your fact table. In my example above, the column is called 'sales' in the 'transaction' table. 

      Using this approach, drag the 'model attribute' element block from the menu and use it in your formal, and then choose that column. If you cannot see the column, it needs to be unhidden in the model. If you build a virtual column (directly into the model tools), you should see it whether its hidden or not. 

Content aside

  • Status Answered
  • 1 Likes
  • 12 days agoLast active
  • 8Replies
  • 44Views
  • 3 Following