Solved

Percent of total for distinct values

  • 24 February 2021
  • 5 replies
  • 264 views

Hi all,

I am aware that lot of questions have been asked about % of total but I could not find the specific question I am looking for.

Sample data & explore

Total active email addresses: 50

I have four activities with 25 email addresses undertaking each activity with some email addresses undertaking multiple activities. A simple table calc of value/sum(value) gives me % of the table total whis is 50% each. I am looking for % of total against Total active email addresses. 

Is there a way to achive this without making a database change or a derived table? A merged Look could work; I am trying to see if this can be achieved in a single Look.

 

Much appreciated.

Regards,

Sandeep

icon

Best answer by Maddie 24 February 2021, 15:39

Hi sandsp,

 

The Totals available in the Data tab in the explore actually calculates distinct values across all rows, see here for how to use the totals within the explore. This help article explains this behaviour in more detail.

 

Using the Total + a table calculation which calculates ${count_distinct_email_addresses}/

${count_distinct_email_addresses:total} would give you what you are after.

 

Let me know how you get on!

 

Best,

 

View original

This topic has been closed for comments

5 replies

Userlevel 3

Hi sandsp,

 

The Totals available in the Data tab in the explore actually calculates distinct values across all rows, see here for how to use the totals within the explore. This help article explains this behaviour in more detail.

 

Using the Total + a table calculation which calculates ${count_distinct_email_addresses}/

${count_distinct_email_addresses:total} would give you what you are after.

 

Let me know how you get on!

 

Best,

 

Hi Maddie,

That helped. Thanks!

Regards,

Sandeep

Hi,

 

Any way to do this in LookML?

The reason is we’re trying to set queries that are shown in the quick start where table calculations cannot be preset.

 

Thanks!

Userlevel 3
Badge

@ErikI  Yes, there is a measure type percent_of_total that you can use in lookml.

Here the documentation: https://docs.looker.com/reference/field-reference/measure-type-reference#percent_of_total

Thanks @David_P , I had tried it but it didn’t work well with a distinct and a pivot giving incorrect results.

What I did find out was that the default behaviour when used in a pivot is to count a total per row, so this little parameter solved it!

direction: "column"

Description