How can I divide two parameters in Google Data Studio?

user_3050550 0
Viewed 4255

My data set (Google Sheets) is:

user_email current_step
user1@example.com 1
user1@example.com 1
user2@example.com 1
user3@example.com 1
user3@example.com 0
user4@example.com 0

I want to take the number of instances that meet a criteria, i.e. COUNT_DISTINCT(current_step = 1), and divide that result by the total number of users in my data set, i.e. COUNT_DISTINCT(user_email).

For reference in case it helps, the Excel equivalent (assuming user_email in Column A, current_step in Column B):

=COUNTIF(B2:B,1)/COUNTA(A2:A)

The expected output (Google Sheets) would be 4/6 = 0.67 (67%):

user_email current_step COUNTIF(B2:B,1) COUNTA(A2:A)
user1@example.com 1 1 1
user1@example.com 1 1 1
user2@example.com 1 1 1
user3@example.com 1 1 1
user3@example.com 0 0 1
user4@example.com 0 0 1

Google Data Studio report

google-data-studio
2 Answers

0) Summary

Use either #1 (recreates the original formula) or #2 (provides an alternative calculation that considers unique values):

  1. Based on the formula in the question: 4/6 = 67%
  2. Similar to #1 with the change being that COUNT_DISTINCT(user_email) is used as the denominator instead of COUNT(user_email)) which results in 4/4 = 100%

1) Denominator: COUNT(user_email)

COUNT(CASE current_step
    WHEN 1 THEN current_step
    ELSE NULL
  END ) / COUNT(user_email)

2) Denominator: COUNT_DISTINCT(user_email)

COUNT(CASE current_step
    WHEN 1 THEN current_step
    ELSE NULL
  END ) / COUNT_DISTINCT(user_email)

Publicly editable Google Data Studio report (embedded Google Sheets data source) and a GIF to elaborate:

user_128925530

This is my work around, for a custom field:

SUM(IF(current_step=1, 1, 0)) / COUNT(current_step)
user_5641570
Related