AdWords is now Google Ads. Our new name reflects the full range of advertising options we offer across Search, Display, YouTube, and more. Learn more

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

Get new metric calculated AFTER aggregation

[ Edited ]
Visitor ✭ ✭ ✭
# 1
Visitor ✭ ✭ ✭

Let's say I have a data source like this :

 

date | gender | number

...

2017/06/21 | Male | 3456 

2017/06/21 | Female | 2481

2017/06/22 | Male | 1672

2017/06/22 | Female | 1293

...

 

I would like to plot the ratio # of males / # of females.

 

According to me, it is currently impossible to create a new metric based on that.

 

My feeling is that calculated metrics are only possible on the same "line" of data, as they are calculated BEFORE the aggregation happens... I am wrong?

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Marked as Best Answer.
Solution
Accepted by topic author Guillebert d
June 2017

Get new metric calculated AFTER aggregation

Explorer ✭ ✭ ☆
# 6
Explorer ✭ ✭ ☆

you can't do it in GDS, you have to do it in your data source, assuming you can manipulate data there.

View solution in original post

Get new metric calculated AFTER aggregation

Explorer ✭ ✭ ☆
# 2
Explorer ✭ ✭ ☆

you are right, what you can do though, is add a column in your data source, with total = male +female 

 

then you can get your ratio.

 

for me this is the biggest limitation of GDS, the calculated field formula is very very limited compared to the like of PowerBI and Qlik, I hope they are working on that.  a simple sum_total function will make a huge difference. 

Get new metric calculated AFTER aggregation

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

Sorry I don't understand your solution. Male and Female are the possible values for the dimension "gender". I can not sum them in GDS.

Get new metric calculated AFTER aggregation

Explorer ✭ ✭ ☆
# 4
Explorer ✭ ✭ ☆

something like this

 

date             gender        number   total

21/06/2017   Male           3456.00  5937

21/06/2017   Female       2481.00  5937

Get new metric calculated AFTER aggregation

Visitor ✭ ✭ ✭
# 5
Visitor ✭ ✭ ✭

What would be the function of that calculated metric ?

Marked as Best Answer.
Solution
Accepted by topic author Guillebert d
June 2017

Get new metric calculated AFTER aggregation

Explorer ✭ ✭ ☆
# 6
Explorer ✭ ✭ ☆

you can't do it in GDS, you have to do it in your data source, assuming you can manipulate data there.

Get new metric calculated AFTER aggregation

[ Edited ]
Visitor ✭ ✭ ✭
# 7
Visitor ✭ ✭ ✭

Ok tks, I already knew that :-). According to me, this is a HUGE limitation of GDS if you want to connect directly to Google Analytics for example.

Get new metric calculated AFTER aggregation

Explorer ✭ ✭ ☆
# 8
Explorer ✭ ✭ ☆

calculated field formula is one of the biggest limitation in GDS, I can't even return a null instead of zero, Google should know better Smiley Sad

Get new metric calculated AFTER aggregation

Visitor ✭ ✭ ✭
# 9
Visitor ✭ ✭ ✭

 

I think this will work if you take the following steps:

 

1. Create a calculated column in Data Studio called `male_count`. This can be done with the `CASE` function:

    CASE

        WHEN gender="Male" THEN number

        ELSE 0

    END

2. Repeat step 1, but for`female_count`.

3. Now you should be able to have Data Studio aggregate these values for each date by creating another calculated metric as follows:

    `SUM(male_count) / SUM(female_count)`

4. Graph this metric in a time series and voila!

 

I haven't tested and proven this, but I think it will work.