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

Pivot dimension in datastudio

Visitor ✭ ✭ ✭
# 1
Visitor ✭ ✭ ✭

 

I would like to calculate the number of fields with a specific value so I can use them as columns in a table.

In BigQuery you can use something like this in a SELECT:

SUM(IF(type = 'a', 1, 0)) AS a_count,

SUM(IF(type = 'b', 1, 0)) AS b_count

 

I would like to show a_count and b_count in a table where the rows are another dimension.

Is something like this possible in datastudio?

 

 

1 Expert replyverified_user

Re: Pivot dimension in datastudio

Google Employee
# 2
Google Employee
Hi Maarten,

Yes you can do exactly what you want (I hope!) using this syntax:

Sum(CASE when x then y else z End)

So create a couple of calculated fields and do something like this:

First field (a_count): Sum(CASE when type = 'a' then 1 else 0 End)​
Second field (b_count): ​Sum(CASE when type = '​b' then 1 else 0 End)

Hope this helps!

Rick

Pivot dimension in datastudio

[ Edited ]
Visitor ✭ ✭ ✭
# 3
Visitor ✭ ✭ ✭

Hi Rick,

 

thanks for the support, this is really helpful.

However I cant find a method to do the pivot by dimension and sum the values of the desired dimension.

 

Some simple example:

 

SUM(CASE WHEN ga:channelGrouping  = 'Organic Search' then ga:sessions else 0 END )

 

I want the sessions by this dimension, there seems to be no way to do this so far?

 

Pivot dimension in datastudio

Google Employee
# 4
Google Employee

Hi Michael,

 

Unfortunately, that particular example won't work. You can't return a metric in a CASE statement, only dimensions or literals. I'm not sure if this would work for you, but if you just want to show the sum of sessions for organic searches you could place a Scorecard on the report using gaSmiley Frustratedessions as the Metric and put a filter on the ga:channelGrouping Dimension = "Organic Search"

 

Hope this helps!

 

Rick

 

Pivot dimension in datastudio

Visitor ✭ ✭ ✭
# 5
Visitor ✭ ✭ ✭

Hi Rick,

 

thanks, I'll try that out. There is always a workaround Smiley Happy
However the pivot is a very helpfull way making new datatables of unavailable dimensions (as a combination of sum and filter). Maybe you will consider implementing this feature one day?

 

Best,

Michael

 

Pivot dimension in datastudio

Google Employee
# 6
Google Employee
Hi Michael.
 
Yes, we are looking into adding this feature. Thanks for your patience (and willingness to do work-arounds!) in the meantime.
 
Regards,
 
Rick

Pivot dimension in datastudio

Follower ✭ ☆ ☆
# 7
Follower ✭ ☆ ☆

Hi Rick,

 

This is also something I would like to do.

Imagine, I have a metric sessions and some dimension called "Constraint1" and this would either have a value or not (we can also make a calculated dimension here that returns "true" or "false").

 

I do now want to create a ratio calculation - between "all sessions" and "all sessions" with "Constrain1" set to something (or use a calculated dimension and just take all sessions with the calculated dimension to be "true").

 

This seems to be impossible at the moment.

 

Thanks!

Pivot dimension in datastudio

Visitor ✭ ✭ ✭
# 8
Visitor ✭ ✭ ✭

Hi Rick, 

 

I am looking a way to create a custom metric that allows me to add a filter in its definition. 

 

it seems what you were discussing in this thread is what I am looking for. 

 

any idea of the status of this feature?

Pivot dimension in datastudio

Google Employee
# 9
Google Employee

Hi Elmer,

 

In Data Studio, filters are things you apply at the report level (after the dimensions and metrics are defined). You can't currently filter a metric, but we are working on allowing that.

 

The solution I proposed earlier is the way to create custom metrics based on conditional values. I include that again here:

 

Sum(CASE when x then y else z End)

So create a couple of calculated fields and do something like this:

First field (a_count): Sum(CASE when type = 'a' then 1 else 0 End)​
Second field (b_count): ​Sum(CASE when type = '​b' then 1 else 0 End)

 

You can then create ratios or calculations using a_count and b_count.

 

Hope this helps!

 

Rick

Pivot dimension in datastudio

Visitor ✭ ✭ ✭
# 10
Visitor ✭ ✭ ✭

Hi Rick, 

 

thank you for your quick reply, 

the solution doesn't work for me since I need to do a case by a dimension but return a metric, that is not possible. 

 

Also i would need to SUM the values, that is also not allowed. 

 

Thank you, 

Elmer