6 Replies Latest reply on Nov 24, 2012 9:30 AM by Stephen Jones

# Calculation to count unique occurrences of text strings

I am looking for a way to 1) count the total unique occurrences of text values in a dimension, then divide a measure by that result.

The attached file shows faculty members who taught classes in a given semester. I want to total the unique faculty (in this case the result would be 4 distinct faculty members), and then divide the total Student Credit Hours (323) by that number (4) to get the average Student Credit Hours per faculty member (which should be 80.75). How can I do this?

I'm a new Tableau user. Please forgive my lack of understanding. I've tried to read quite a bit, but have not been successful as of yet.

Many thanks!

Stephen

• ###### 1. Re: Calculation to count unique occurrences of text strings

Hi Stephen, you'll need to post a packaged workbook (twbx). The workbook you posted (twb) doesn't have any data in it.

--Shawn

• ###### 2. Re: Calculation to count unique occurrences of text strings

Thanks, Shawn. Hope this works.

Stephen

• ###### 3. Re: Calculation to count unique occurrences of text strings

Stephen, the formula basically is:

SUM([Student Credit Hours])/COUNTD([Primary Instructor])

Which gives you the totals you're looking for in your table (see attached). Hope this helps.

--Shawn

• ###### 4. Re: Calculation to count unique occurrences of text strings

Thank you very much, Shawn. I get it!

In my much larger data set the Excel file has not been imported – it's a live connection to that Excel file – and to use COUNTD I understand I need the data to be imported into Tableau. How do I convert that Excel file (import it into the Tableau file) so that COUNTD is available to me as I perform calculations on the whole file?

Sorry for all the newbie questions.

Stephen

• ###### 5. Re: Calculation to count unique occurrences of text strings

Stephen, not a problem, we were all newbies. Right-click your data connection, then click Extract Data. This pops up a dialog box. This first time (and most times) go ahead and accept all the defaults by clicking OK. That does it, you'll now be using Tableau's data engine, instead of MS Jet. And now COUNTD() is available. It's also going to be faster. Just remember that is your under-lying data changes you'll need to refresh or change the extract.

Good luck.

--Shawn

1 of 1 people found this helpful
• ###### 6. Re: Calculation to count unique occurrences of text strings

Again, many thanks, Shawn.

It's early, but here's my gift to you – a Christmas music video. I am the composer. The YouTube version hasn't been seen as much as a previous version on line that reached hundreds of thousands. Hope you enjoy it. Forgive me if you are not a Christian.