Percent of total for distinct values

sandsp
Participant II

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

f3561fb5-2818-4b04-9837-d24abd691156.png

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

Solved Solved
0 5 1,684
1 ACCEPTED SOLUTION

Maddie
Participant IV

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 solution in original post

5 REPLIES 5

Maddie
Participant IV

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,

sandsp
Participant II

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!

David_P1
Participant V

@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_P1 , 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

Top Labels in this Space
Top Solution Authors