5 Replies Latest reply on Oct 7, 2013 12:55 PM by Matt Lutton

# Show the number of sub-categories that have at least x amount of sales

I have what seems to be a simple calculation, but I'm not getting the answers I need.  I have Categories of customers.  Within each category is a list of customers, each with a total sale amount.  I'm wanting a report that shows the number of customers within each category that meet a certain threshold of sales.  A sample output would be as follows:

RegionNumber of customers with at least \$10,000 of sales
East5
West10
North8
South13

I don't need to show the sales amount, just the count of those that meet the threshold within each category.

• ###### 1. Re: Show the number of sub-categories that have at least x amount of sales

Have you tried writing a calculated field, like:

COUNTD(IF sum([Sales])>=10,000 then [CustomerName] END)

(give me a count of unique customer names that have sales greater than or equal to 10,000)

If you place "Region" on the Rows shelf, and this calculation on the Rows or Text shelf, you should get the results you are looking for

1 of 1 people found this helpful
• ###### 2. Re: Show the number of sub-categories that have at least x amount of sales

Matthew,

I got a couple of errors from your calculation, but I was able to follow your logic.  Tableau doesn't recognize the COUNTD function, I got an error about mixing aggregate and non-aggregate comparisons, and the IF needed an END keyword.  I changed the calculated field to the following, and it worked.

COUNT(IF [Sales] >= 10000 THEN [CustomerName] END)

Thanks for your help.

• ###### 3. Re: Show the number of sub-categories that have at least x amount of sales

Yes, if you are using an Excel data source, you must Extract your data in order to use the count distinct (COUNTD) function.  This is important to note, because COUNT will include duplicates, while COUNTD will return the unique count.  So, if what you need is Count Distinct, you'll need to extract your data.  Also, you are correct, any IF condition will need an END to be valid in Tableau.

• ###### 4. Re: Show the number of sub-categories that have at least x amount of sales

Thanks.  That did it.

• ###### 5. Re: Show the number of sub-categories that have at least x amount of sales

Not a problem at all, glad its working.