Build a histogram with programmatically named bins

  1. Add a table summary to find the absolute maximum of your column of interest. I’m interested in binning Profits, so I’ll define [Absolute Max of Profit] as Max([Profit]).

  2. Add a new column called [BinFixed of Profit] into the base columns of your table. Use the following formula to return bin numbers associated with each value in the column of interest:

    • BinFixed([Profit], 0, [Absolute Max of Profit], 99).
    • Feel free to use BinRange instead if you would prefer to specify bin ranges.
  3. Group by [BinFixed of Profit] and add 4 new calculation columns in this grouping level.

    • In your first calculation column, find the [Min Profit] using the formula: Min([Profit]).
    • In your second calculation column, find the [Max Profit] using the formula: Max([Profit]).
    • In your third calculation column, find each [Bin Name] using the following formula:
      • Concat("$", Text(Round([Min Profit], 0)), " - $", Text(Round([Max Profit], 0)))
    • In your fourth calculation column, find the [Row Count] per bin by using the formula Count().

  4. Create a child visualization element (go with the default bar chart).

    • Drag the [Bin Name] column into the X-axis.

    • Drag the [Row Count] column into the Y-axis.

    • Right-click the [Bin Name] column in the X-axis section of the left-side panel and select “Sort” > “Custom sort” from the menu.

      • When the custom sort modal appears, sort by [BinFixed of Profit] ascending (use the “Min” or “Max” aggregation).

Ta da! :tada: Now you’ve got a histogram with named bins. How easy was that!?

Please note that you also could have used the Choose function to name the bins, but that would have required hard-coding bin names. This would make more sense to do if you had a small number of bins (especially if you defined bins using BinRange - in this case, you’d already know the range of each bin).

1 Like