Count Distinct IF criteria is met | Calculate Unique Count Where Condition [Data Studio Tutorial] 

HII! This week I'm back with calculated field tutorial again! If you prefer reading, here's the blog post: danalyser.com/posts/data-studio-count-distinct-with-condition/ Come hangout at my Ko-Fi page too ! ko-fi.com/T6T25PD0A -SL

Elegant solution for a quite painful problem. Thanks a lot @Danalyser, you really deserve it.

Your solution saved me a huge amount of time ! Thank you so much !!!!

Hi Danalyser- You teach me here how to fish. Respect as a teacher😃

This solution totally saved me. Thanks!

Thank you! this saves me some hours of reseach :)

Thank you Dana! Saved me a lot of time and energy from adding a new dataset T_T

Thanks a lot Dana ♥. I fixed my problem. Instead of dividing # of sold items / # of transaction. I divided # of sold items / count_distinct (transaction_id)

Very useful video! Been crunching this problem for 3 hours, thanks for saving me 🖤

Exactly what I was looking for!!! Thank you so much!

Thank you for sharing the knowledge Danalyser 🙏🏾

This was so helpful. Thank you for the video

Fantastic. Crisp explanation ❤

This is great! Thank you

You just saved my day! Great work and thank you for all that you do!

Thank you for the video @danalyser. It helped me a lot. I am curious to know if you can use the same kind of formula from your tutorial but instead of using a specific text as the condition, use a lookup range as a condition. So for example count if email from table 2 features in table 1. Count(IF(customer_email (Table 2) = customer_email (Table 1), 1, Null))

This is a great hack! I use it to calculate % by column in a table

Excellent

Thank you ❤

Thank you!

Hi, thanks for the video!

Seriously, thanks a lot :)

congrats for your channel

This video really help me to solve my current problem for my dashboard....thanks ya...

Hi! Thank you for the video, this is exactly what I've been looking for!

thanks! I like your vdo :)

Thank you for sharing

Exactly what i am looking for, thanks🙂👌

I'm sorry guys! I didn't know my audio is distorted all this while 😱😱 just discovered it today .. I'll use earphone for any editing from now onwards. 😢

Thank you for this video! I need little help with the formula " 1 - COUNT_DISTINCT(IF(Disability Label= "Undeclared",Personal Reference, NULL)) / COUNT(Disability Label) * 100 to get the percentage of disability declared. Percentage number of undeclared take away from 1.

HI THANKSSS!!

Hi! Thank you for the video, i have other problem, i need to do a Sum Distinct, you know how to do it?

hello, what if I want to combined 2 categories at the same time for the same chart Column Chart? I tried your methods, worked fine for a single category, but didn't work out for double category. Thanks!

Thank you for the excellent video. When I apply your method to my dataset I can calculate the number of "stationary sales," as a metric but when I try to use that number as divisor, with the numerator being another metric (say 'order amount' if that were in your dataset) Data Studio gives me an error about mixing metrics (aggregated values) and dimensions (non-aggregated values). Is there a workaround for this?

I have a question, but firstly thank you for this tutorial - very useful as it's hard to find visual examples of these things for GDS My question/challenge is that I've created the count distinct for my topics (Product Category in your vid) and I want to divide that by a static number for each of the topics For example Topic 1 needs to be divided by 10 Topic 2 needs to be divided by 20 Topic 3 needs to be divided by 30 If my COUNT_DISTINCT returns 10 items for Topic 1, 15 items for Topic 2 and 20 items for Topic then then I would want to divide them (Topic 1 = 10/10, Topic 2 = 15/20 and Topic 3 = 20/30) How would I achieve that? Thank you

I am trying to return the number of distinct states where total sales are greater than 500. The data is transactional, so it would require first calculating total sales (aggregated data) and then collecting the distinct count of states with the totalsales > 500 value. Since this logic violates the aggregate / non-aggregate rule, what's the best way I can achieve this?

I noticed you have several dates in your data set. Do you have issues with inaccurate reported numbers in Looker Studio when showing with this when comparing "Orders Placed" vs "Orders Shipped Out" during a certain time period? I am. Also, this problem translates to building a % formula for Orders Placed/Orders Shipped Out.

i have created a category from metric nut it is taking the category as metric i want it as a dimension, i tried cast function but it did not helped, can you help me?

Hello! Nice video, very interesting! I would like to adapt it to what is my need, I would like to create a field that would count the unique events for a given event action, is your formula usable? Example, I have single event click-btn, I want a field that gives me the number of unique events it has triggered, how can I do? With the classic method of filters I know how to do it. My problem is that I have several events and I should insert them all in a table (so each column a event with unique events triggered). Thank you very much :)

Great video. My question is what if I want to count the number of stationary and another product say "Audio" and "Tech Gadget". How do I do it?

1 - (COUNT_DISTINCT(IF(Disability Label= "Undeclared", Personal Reference, NULL)) / COUNT(Personal Reference) * 100) the formula do not give desire output as it give -99 in all rows.

Hi thank you very much for your video, it helps me a lot! I have a question about on how to count a duplicate data per row and give you the sample data in your email. It'd be my pleasure if you would give me some help with that. Hope you're healthy and happy always!

can we do sum_distinct in Looker

why is there no add field in my display?

Hi SL, love your tips. Does Looker Studio allow you to do IF(OR( statements? Can you do IF(AND( statements like in excel and Big Query? E.g. COUNT_DISTINCT(if(AND(fiscal_year = 2024, Quartile =1), parent_client, null)) I can use the filter, but Means I have to make lots of filters for each Quartile. Side note, I don't have any date values in my data as year and Quartile = integer, financial periods = string.

Hi, I have a question. I have hundreds of products and I wanted to create a pie chart that groups products by how much they sold that week. So the pie chart would be the total sales, and there would be 4 groups: Products with $0 Sales, Products with $0-$100 in Sales, Products with $100-$200 in Sales, Products with More than $200 in Sales. I can't seem to find any solutions for this. I made a field that used Case data that grouped the Product Revenue data together into the above buckets, but I could only use it as a metric. I would like to make those groups dimensions, so I could see how many of my products fall within those groups.

Hi Danalyser, was wondering if you could assist me with an issue I havent been able to solve in data studio. I cant seem to find a way to message/email you. Am willing to pay for a solution

Thanks! Very helpful.