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
-
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:
-
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.
-
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? -
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.
-
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.
-
-
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+" )-
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.
-
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.
-
