Count Distinct IF criteria is met | Calculate Unique Count Where Condition [Data Studio Tutorial]
Вставка
- Опубліковано 30 чер 2024
- 👉 👉 This video shares 2 methods to calculate distinct numbers when ONE or multiple criteria is met. We'll also explain in detail one of the common error faced by beginners in creating calculated fields in Data Studio.
You may replace the COUNT_DISTINCT formula with COUNT(), or IF() formula with CASE_WHEN() formula based on your use case. The fundamental concept is the same once you understand how it works in Google Data Studio.
(Timestamp provided below if you wish to revisit certain chapters)
Does this tutorial helpful? Lemme know in the comment below 👇👇👇
F R E E B I E S
I used Canva to make graphics for my education videos and thumbnails. If you want to try this amazing tool, you are welcome to use the link below!
Try Canva Pro FREE for 30 days: partner.canva.com/danalyser
[ This is an affiliate link. I receive a small commission if you decided to subscribe to the Canva plan after trying it out. There's no extra cost to you.]
𝐖𝐚𝐧𝐭 𝐭𝐨 𝐬𝐮𝐩𝐩𝐨𝐫𝐭 𝐦𝐨𝐫𝐞 𝐜𝐨𝐧𝐭𝐞𝐧𝐭 𝐥𝐢𝐤𝐞 𝐭𝐡𝐢𝐬 ?
👉 You can now buy me a coffee @ ko-fi.com/T6T25PD0A
👉 Subscribe to my channel @ / @danalyser
#Google #DataStudio #Tutorial #Date
--------------------------------------------------------------------------------------------------------------------------
⏰ Content Outline ⏰
⏩ 0:00 Dataset Overview and Example
⏩ 01:15 Method #1 : Simple and Easy
⏩ 02:54 When to use method #2 ?
⏩ 03:06 Error Message Explained + Error Debug
⏩ 05:22 Method #2
** Blog Post: danalyser.com/posts/data-stud...
📘 Google Data Studio Formula Documentation:
‣ COUNT_DISTINCT - support.google.com/datastudio...
‣ IF- support.google.com/datastudio...
‣ CASE WHEN - support.google.com/datastudio...
--------------------------------------------------------------------------------------------------------------------------
👧🏻 The person behind this channel is a Malaysian-Chinese Data Analyst. Aspired to create content for non-technical background audiences to up their game in their careers or even for students to prepare for their future career at early stages.
🌱 Learn something today and want to learn more?
👧🏻 SUBSCRIBE NOW 👉 / @danalyser
👧🏻 FOLLOW ME on TWITTER for more content (some content are exclusive to Twitter) 👉 / danalyser8
---------------------------------------------------------------------------------------------------------------------------
💌 MY SUGGESTED VIDEO FOR YOU:
⚈ How to work with timestamps in Data Studio?
👉 • Google Data Studio Tut...
⚈ Export Data from Data Studio
👉 • How to Export Data Fro...
⚈ Automation in Data Studio (Email Delivery)
👉 • How to automate data s...
⚈ Master Filter in Data Studio
👉 • Google Data Studio Tut... - Навчання та стиль
![Display TODAY's DATE as a TITLE in Data Studio [Calculated Field: CURRENT_DATE, MAX, CONCAT]](http://i.ytimg.com/vi/EVQ_nW19eq0/mqdefault.jpg)
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
In case you're looking for CASE WHEN tutorial: ua-cam.com/video/ljmfdcmR_iY/v-deo.html
Enjoy
Very useful video! Been crunching this problem for 3 hours, thanks for saving me 🖤
Elegant solution for a quite painful problem.
Thanks a lot @Danalyser, you really deserve it.
Aww THANK YOU, you're so lovely! ❤️
Thank you for sharing the knowledge Danalyser 🙏🏾
Exactly what I was looking for!!! Thank you so much!
Glad that it helps! 🥰🥰
Hi! Thank you for the video, this is exactly what I've been looking for!
Hey Danila, thanks so much for leaving this comment! Glad that you find it helpful :)
If you have any video ideas, you're welcome to leave me a message here :)
- SL
Your solution saved me a huge amount of time ! Thank you so much !!!!
You're SO welcome! Thanks for your support too! :)
You just saved my day! Great work and thank you for all that you do!
WoW THANK YOU!! 💛💛🙌🏻
This is great! Thank you
You are very welcome!! 💛
This was so helpful.
Thank you for the video
So glad you find it helpful Kesiena ! :)
Thank you Dana! Saved me a lot of time and energy from adding a new dataset T_T
Glad it helped! 😊
Hi Danalyser- You teach me here how to fish. Respect as a teacher😃
Glad to know that!! Thanks for your lovely comments! 😊😊
Thanks! Very helpful.
Glad to know that it was helpful Andres! :)
This solution totally saved me. Thanks!
Great to hear!
Fantastic. Crisp explanation ❤
Glad you liked it! 😇
Thank you! this saves me some hours of reseach :)
Glad to hear this Jorge! :D
SL
Exactly what i am looking for, thanks🙂👌
Cool! You're so welcome Thapelo, glad that it was helpful to you :)
Hi, thanks for the video!
You’re very welcome Steven! I’m preparing some videos to be back to UA-cam. Feel free to suggest new videos to me that you’re interested to see! Have a great day🤓
Thank you for sharing
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)
Great job! So happy that this video helps solve your problem! 🎉 Thanks for sharing, appreciate it! 💛
Seriously, thanks a lot :)
You're so welcome Mariel ! :) Feel free to share this with anyone that'd find this video useful ⭐️
Thank you ❤
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))
Hey Jannik, thanks for the great feedback! I can only imagine this can be done when you left join table with the loookup table to be able to apply this formula :)
Excellent
This video really help me to solve my current problem for my dashboard....thanks ya...
Hey! You're most welcome! Good to see you around :)
Just curious, are you also working as data professionals? :)
Not really... but my work deal with data...and still learning thru youtube...
@@mohdzulfadhlimj9662 I see! I hope these videos are helpful for you. Feel free to share any content you would like to see from this channel so I could include in my planning 😊
This is a great hack! I use it to calculate % by column in a table
YASSS I used to do that as well, that creates LOTS of dummy columns 😅 Sho excited to found this solution and share it with you all! 🙌🏻
Thank you!
You’re welcome!
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.
congrats for your channel
Thanks alot!! 🙏🏻
HI THANKSSS!!
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. 😢
Hello, thank you for this great video ! Do you know if it is possible to do the same thing without having a unique identifier for each line (like Transaction ID in your example) ?
Hey Thomas! You’re welcome! 😊
You meant to get count distinct of the dimension without the unique identifier ? Not possible I would say.
Or if you actually wanted the count rows while assuming each row is unique ?
Hey, thank you for your quick answer.
I think I might be lucky and fall into the second case but I'm not so sure. Let me explain my problem :
I'm extracting data from google ads. I want to count conversions per date, but I have different conversion type (let's say type A and type B) and I want them to be separated.
I have the dimensions/fields "all conversions", "Conversion type" and "date".
My goal is to count conversions with the condition "conversion type = "type A" and be able to say "The 1st of january we had 30 conversions of type A and 15 conversions of type B" instead of "We had 45 conversions". But I don't have a unique identifier like in your video. Is it still possible ? I feel like it should be but I can't find the function I am looking for.
Please let me know if that is clear enough ☺
thanks! I like your vdo :)
You’re welcome! happy that you enjoy these videos 😁
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?
Thank you for watching Will!
Yes, we can't mix unaggregated and aggregated fields in a calculation formula in GDS. In this case, for the divisor, you can use the formula SUM(order_amount).
Hope it helps ! :) -SL
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!
Hi! Thank you for the video, i have other problem, i need to do a Sum Distinct,
you know how to do it?
Hi Satrack! You're welcome! :)
To answer your question, do you mind help me elaborate more what is ur problem ? maybe give a small sample of how your data looks like would be great.
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?
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 :)
Hey Davide, thank you for the feedback! I'm not sure whether this formula would fit what you need, may have to take a look on the dataset you have and understand further about your requirement (a table with several types of events count - if i understand your sentence correctly.)
I'm currently offering technical consultation for GDS, we can discuss further via email if you wish. You may also contact me at theartclover@gmail [dot] com.
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.
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.
Yes, you can use Case When, For example SUM(CASE WHEN Reporting Year = 2022 THEN Cost ELSE 0 END)
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?
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
Hey Daniel, Thank you so much for your comments! I am glad that you appreciate the work I put into the video :)
What I would do is to create an extra calculated field for divisor using CASE WHEN formula, then I would do the COUNT_DISTINCT(Topics) / AVG(divisor).
I hope this helps or at least give you some idea on how to solve the problem. :)
In case you need further private help, I have begin to provide technical consultation service for google data studio - you may reach out to my email (hello@danalyser.com) for more info.
Happy Dashboarding!
- SL
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!
Thanks again Atikah, nice talking to you earlier and hope to see you around more ! Happy learning 😊
@@danalyser Thanks for the help! It’s also great talking with you😊😊
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?
Hey Mike, glad you find it useful ! You can use IF() and AND operator, this documentation may be helpful to you: support.google.com/datastudio/answer/10468770?hl=en
@@danalyser Thank you so much for this reply. Although, I've tried the AND operator with my data it returned 0 even though there were customers who bought two products at one time. I know you'd probably be busy, but if you are available I would love to share my dataset with you to help me solve this problem.
Either way, thank you so much for this video and the reply.
Mike.
@@mikedechelsea hey Mike, sorry to hear that it’s not working for you.
I do accept freelance work now which charged by hourly rate. If you’re interested, you may get in touch with me at hello@danalyser.com for more info :)
why is there no add field in my display?
can we do sum_distinct in Looker
you could with the similar concept shared in this video, you may feel free to check out this SUMIF tutorial: ua-cam.com/video/hkNVgPjjoHA/v-deo.html&lc=Ugxmu1_SrFy413J6opp4AaABAg
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 Leo 👋 we’ll need a bit more steps to do this, let me try to address this in a video ? Will comment here again when the video is up 😊
Hello, @@danalyser, how are you? Could you explain to me how I could solve this problem, please?
Hello, Leo, did you find any way to solve your problem? Could you help me? I'm in the same situation right now
@@gabriellourenco6136 hi thank you for asking! I’m doing good. The video is already up in my channel: ua-cam.com/video/FB4R61L1FgM/v-deo.html
@@danalyser Thank you!!! You're awesome!
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.
Hey Banji, any particular reason you put COUNT(Personal Reference) instead of COUNT_DISTINCT(Personal Reference) - I wasnt too sure what value you have in this field, could you share more so I could make some suggestions based on that? :)
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
Hey Joseph, yes I’m willing to help. Do share me the problem you face and We’ll see if I’m able to provide some help to you. you may contact me at my instagram pm - @danalyser8