OBIEE - Grouping measures and counting group members

Few days ago I was asked for something like this: "We have a table of Revenue for each Product. How can the end user assign the values of Revenue into groups he invents and then count the number of Products in each group." 

It seems easy but, try it in SQL. It's not trivial at all. 

For some reason I had a blackout. Since I reinvent the answer to similar questions for the third time at least; It's time to write about it here. The process actually takes about 3 minutes, but it will be a bit long post, since it's a very detailed explanation.

We start with the basic analysis: Product and the Revenue (twice). One Revenue column to work with and the other for QA.

 Next edit one of the Revenues formula and select "Bins". Here we can easily create groups, we could otherwise create by a big manual case statement.

I created a bin named "1. Under 100K" with condition of Revenue<100000.

It's important to uncheck the "Treat result as numeric value ..." since the result is textual.

otherwise you will get the following error:

I created few bins and then created the last one by marking "create a bin of all other values".

Since I want to use the result of the bins as attribute later (count the number of Products in each bin), I'll check the "Treat as an attribute column",at the bottom of the function screen.

I named the column "Bins".

In "results"Tab, we have a result table, I'll delete it and create a Pivot table.

Now it's time to edit the pivot:

I will switch the Products column and the Bins column in the Pivot Table:

 

First I'll move the Bins column to the left axis of the pivot:

Next move the Products column to the Measures section of the Pivot:

Of course the Product Names can't be aggregated and are left blank in the Result.

So it's time to click the Products column and select "Aggregation Rule" and "Count":

Now it's perfect.

If you prefer you can exclude the regular Revenue column or add a Chart:

This is the result: