How to hide percentiles (bottom 10% etc.)

Looker Tips & Resources Technical Tips & Tricks
1

Knowledge Drop

Last tested: Mar 26, 2020

The Problem

How can I hide the top 10% of my measures? It can seem simple, but it can be completed very easily with the right table calcs.

A Solution

We we create three main table calcs (they can be grouped into a single table calc if you like as well)

  1. First create the measure that you will be using.
  2. We will create the percentage of the whole.
    Basically, what against the entire columns value, what percentage is this row’s value
    *${view_name.field_name}/sum(${view_name.field_name})*
  3. We will define what percentile we want to cut. For this example, we will be cutting the bottom 10% so,
    *percentile(${percentage_of_whole},0.1)*
  4. Finally, we will ask a simple question,
    *if(${percentage_of_whole}>${bot_10_percentile},yes,no)*
    Then we will hide the no’s and voila. The no row will disappear.
  5. If you want to create this all in one column its,
    *if(${view_name.field_name}/sum(${view_name.field_name})>percentile(${view_name.field_name}/sum(${view_name.field_name}),0.1),yes,no)*