CODEWITHSUNDEEP
looker-studio
mrbusto71
mrbusto71

Reputation: 47

How can I divide two parameters in Google Data Studio?

My data set (Google Sheets) is:

user_email current_step
[email protected] 1
[email protected] 1
[email protected] 1
[email protected] 1
[email protected] 0
[email protected] 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)
[email protected] 1 1 1
[email protected] 1 1 1
[email protected] 1 1 1
[email protected] 1 1 1
[email protected] 0 0 1
[email protected] 0 0 1

Google Data Studio report

Upvotes: 2

Views: 9135

Answers (2)

Joseph Lust
Joseph Lust

Reputation: 19985

This is my work around, for a custom field:

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

Upvotes: 1

Nimantha
Nimantha

Reputation: 6471

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:

Upvotes: 4

Related Questions