Creating a column percent of total with table calculations

#1

Table calculations enable you to create calculations that operate on the table data returned by a SQL query. This is great for calculating metrics like percent of totals. This post shows how to create a percent of total down a column.

You can also create percent of totals across rows.

Using sum() to calculate percent of total

In order to create a percent of total, you can utilize the sum() function. This will add up all the values in a column. You can then divide each value in that column by the sum. This would look something like:

${orders.count} / sum(${orders.count})

This will give you the percent of total each value is of the column. This will something like:

Using Value Format: #0.0% will format these values nicely for you. Read more about value format here.

Using Look totals to calculate percent of total

As of version 3.24, it is also possible to reference Look totals in table calculations. These can be turned on in a Look by checking the Totals box:

Note that these totals are calculated in the SQL, so they may provide different results than the sum() function in table calculations. Read more about why this is here.

In order to calculate a percent of total using these totals, you can create a calculation like so:

${orders.count} / ${orders.count:total}

This will look something like:

0 Likes

Creating a percent of total across rows with table calculations
RETIRED: Table Calculations (3.18 Labs Feature)
#2

If you want to do something like a running percent of total, you can do this by creating a percent of total calculation like above. Then, using the running_total function to create a running total on that percent of total. Read about using running_total here.

0 Likes

(Michael Erasmus) #3

Is it possible to reference the Row Totals?

0 Likes

(Michael Erasmus) #4

Ah, nevermind. I saw that you can by using row_total instead :smiley: Thanks!

0 Likes

(Jonathan) #5

I notice the value format isn’t used for the relevant axis in the visualisation. Is that a bug or am I missing something?

Also - is there an easy way of working out what the field I need to use in the calculation is called?

0 Likes

(Zachary Michel) #6

@Jonathan

I’ll let engineering know about the value format not trasnpose onto the axis.

In terms of finding out what the field is called - the calculation model should give some recommendations of available fields, and the field’s reference in the table calc is going to be the same reference it uses in the LookML. It will be of the form ${view_name.field_name}.

0 Likes

(Jonathan) #7

Thanks, but I’m afraid I’m still unclear.

If I got to the LookML for the measure, I see a stanza like this:

 - measure: distinct_visits_phone
    view_label: 'Measures'
    type: count_distinct
    description: 'Distinct count of visits (sessions) by users on Phone device'
    filters:
      is_bot: FALSE
      device: 'phone'
    sql: ${visitid}

What is the required information I need to know to get the field’s reference?

0 Likes

(Zachary Michel) #8

@Jonathan the view’s name is part of the reference. For purpose of example, let’s call this view events, which would make the reference for this field’s reference ${events.distinct_visits_phone}. What may also help is if you type the $ the modal will start to suggest possible fields to use in the table calculation.

0 Likes