PowerMetric: Binning your Data

There are times when you want to look at your data by the frequency in which occurs. For example, you have order data and want to see the distribution of order sizes. In the following example the data has orders ranging from $1 to $79, and we'll bin this data into $5 bins to better understand the most popular revenue amounts per order.

1. Create a bin in your model by adding a new column beside you metric column. In our case it is the "Revenue" column. Then you can write a formula to return the upper and lower values in your bin. In our case this will look like:

FLOOR()  will round a number down to the nearest significant value (5). IE. 43 will round down to 40

CEILING() will round a number up to the nearest significant value (5). IE. 43 will round up to 45

NUMBERFORMAT() will ensure that there are no trailing decimals

CONCAT() will join all values into a string.

The result will look something like this:

It is important to note that you should ensure that the column data type is set to string as this column will be a dimension in your PowerMetric.

From here create a PowerMetric and ensure that your bin column is selected as one of the segment columns. Once the PowerMetric is created change it to a bar chart and in the "Over" section you can select the Revenue (Bin) segment to group your data on the x-axis.

Hope this points you in the right direction for another way to slice and dice your data!


1 comment

  • 0
    Rupert Bonham-Carter

    Also referred to by some as "bucketing".  Great article Adam.  Thanks.

Please sign in to leave a comment.