The Problem
Looker visualizations automatically determine which values to plot on a y-axis based on what field types are present in a query. Take, for example, this Explore, which breaks down Users Full Name and Users Age for the top 10 highest Order Count values:
The first two dimensions — Users Full Name and Users Age — are automatically concatenated on the x-axis.
Now, what if we want to plot a dimension on the y-axis when Looker doesn't automatically do that?
In order to do this, we need to transform a dimension into a measure. The two ways to achieve this are outlined in this article.
Solutions
What if we want to separate Users Age and Users Full Name so that Users Age is plotted on the y-axis, and Users Full Name and Orders Count display on the x-axis, like this?
The two following solutions outline how to visualize a dimension, such as Users Age, as a measure so that it can be visualized on the y-axis.
Using a Table Calculation
A table calculation is the fastest and easiest way to plot a dimension on the y-axis. A calculation is typically plotted as a dimension if no measures are involved in the formula. We can make a table calculation behave as a measure by including a measure in the calculation's expression. The key is making sure the measure won't affect the value of the original dimension.
- If there is no measure in your Explore query, add any measure (such as a count), and then hide it. Of course, if there is already a measure in your query that you want to include in the visualization, there's no need to hide it.
- Next, create a table calculation:
- For a numerical field, the calculation will be:
${mydimension} + (0 * ${mymeasure})
The calculation preserves the original dimension value by multiplying the measure by 0. - For a string field, the calculation will involve two logical functions,
if()
andis_null()
, instead:if(is_null(${mymeasure}),${string_dimension},${string_dimension})
This expression tells Looker to always display the value of the string dimension in place of the measure values.
- For a numerical field, the calculation will be:
- Hide the original dimension, since the table calculation now represents the values in the visualization.
Once the table calculation is added and the original dimension is hidden, the Explore will look similar to this:
You can customize how the fields appear with visualization edit options, including using multiple vis types in a single chart. In the example, Orders Count is visualized as a line, and Users Age as a column:
Creating a Sum Measure in LookML
If you plan to plot a specific dimension on a y-axis again in the future, a more sustainable option is to create a sum measure in LookML that sums the dimension you want to plot. Instead of recreating the same table calculation in an Explore multiple times, you can easily add the field to a query from the field selector.
Include both the dimension and the new measure in the Explore query. The numbers will be the same, since the sum will only sum one value — the dimension value right next to it.
The measure will look similar to this:
measure: measure_for_age { ## replace with a new name description: "Use this age field for displaying age on the y-axis" type: sum sql: ${age} ;; ## replace with your dimension }
Adding a description
to the measure can help users understand the intended use of a field.
Hide the dimension in the Explore — but make sure to leave it in the query so the sum doesn't group multiple values. The resulting Explore will look similar to this: