Data Studio
2.1K members online now
2.1K members online now
Ask questions about Data Studio
Guide Me
star_border
Reply

Create new dimension based on metric value

Follower ✭ ☆ ☆
# 1
Follower ✭ ☆ ☆

Is it possible to create a new metric based on a specific dimension value?  In this case I'm trying to create a new metric called "Enquiries" based on Unique Events that have the Event Category "enquiry".

 

I've tried the formula below, but you're not allowed to combine Metrics and Dimensions in a CASE statement.

 

CASE WHEN REGEXP_MATCH(Event Category,'enquiry') THEN Unique Events END

1 Expert replyverified_user
1 ACCEPTED SOLUTION

Accepted Solutions
Marked as Best Answer.
Solution
Accepted by topic author Nick G
May 2017

Create new dimension based on metric value

Google Employee
# 2
Marked as Best Answer.
Solution
Accepted by topic author Nick G
May 2017

Create new dimension based on metric value

Google Employee
# 2
Google Employee

Create new dimension based on metric value

Follower ✭ ☆ ☆
# 3
Follower ✭ ☆ ☆

Thanks again Rick.  I used this formula which did the trick:

 

Sessions * MAX(CASE WHEN REGEXP_MATCH(Event Category, 'enquiry') THEN 1 ELSE 0 END)

 

By the way, I noticed a small bug.  Every time I made a change to the formula and clicked Done, the metric type defaulted to DateTime, even though I'd previously set it as "Number".  It threw me for a while, but spotted it in the end.

Create new dimension based on metric value

Explorer ✭ ✭ ✭
# 4
Explorer ✭ ✭ ✭

Hey Guys,

 

I'm trying to do something similar by matching exactly on a page and returning the Pageviews specifically for that page.

 

In this example I want to return the Pageviews when the Page exactly matches /en-us

 

Pageviews * MAX(CASE WHEN REGEXP_MATCH(Page, '^\\/en-us') THEN 1 ELSE 0 END)

 

However no matter what I do, it always returns the total number of pageviews overall, not just the pageviews exact matching /en-us

 

What am I missing?

 

Thanks!

Create new dimension based on metric value

Google Employee
# 5
Google Employee

Hi Mike,

 

I think this will only work in a table or other chart that includes the Page dimension. I.e., it won't work for a scorecard. Is that what you're trying to do?

 

Rick

Re: Create new dimension based on metric value

Explorer ✭ ✭ ✭
# 6
Explorer ✭ ✭ ✭

Hey Rick,

 

I'm using it in a table, but the table has Hostname as the dimension.

 

The idea being we want to know how many times this page is accessed by individual hostname.

 

I don't want to add the page dimension to the table, because the idea is to have other metrics not affected by page, see screen shot attached (Calculated metric is PDP+PGP).  

 

Is it possible to get a CASE statement to return a value in a table in this manner?

 

Thanks,

 Untitled-1.jpg

Create new dimension based on metric value

Google Employee
# 7
Google Employee

Hi Mike,

 

Unfortunately, the limitation on doing the "conditional calculated field" trick is that in order for the chart to aggregate the data properly, the field used in your test condition (Page, in this case), has to be in the chart. That is documented down at the end of the article I linked to before (https://support.google.com/datastudio/answer/7359285#use-conditional-metrics-in-reports).

 

However, I wonder if you could use a filter on the table instead?

 

Hope this helps!

Rick

Create new dimension based on metric value

Explorer ✭ ✭ ✭
# 8
Explorer ✭ ✭ ✭

Hey Rick,

 

Thanks, I didn't see that blurb at the end.

 

If I were only displaying data for PDP+PGP pages I would just add a regular filter to the table.


However the desired result was to have Pageviews and PDP+PGP in the same table and if I added a filter, it would affect both metrics.

 

That's ok I'll look for an alternate workaround.

 

Thanks