8 Replies Latest reply on Jun 3, 2012 3:08 AM by Jonathan Drummey

# How to count customers with revenues > 10k per year?

How do I count the number of distinct customers by customer segment where their total 2011 sales are at least 10000?  (and be left with only 4 rows, 1 per customer segment, with the total count of customers who have 2011 sales > 10000).

In the example above, I created a calculated field that simply does:

sum([Sales])>=10000

I want to remove the customer from the row and have the count be correct. (the customers who are true).  How do I accomplish this?

• ###### 1. Re: How to count customers with revenues > 10k per year?

Hi Mark,

You can do this by creating a calculated field similar to the following:

if sum(Sales)>=10000 then 1 else 0 end

Right click on the above calculation and select Continuous.

Place this on the filter shelf and filter to 1. Remove Calculation4 from the columns shelf, right click on Customer Segment and click Subtotals. From there, every customer that is removed will update in the subtotal.

Another option is to place Sales on the filter shelf and select sum-->At Least-->10,000.

I am however, unsure of what is meant by be left with one 4 rows. Do you want the top 4 within each segment? If this is the case, this KB article will help get you to that point:

http://kb.tableausoftware.com/articles/knowledgebase/finding-top-n-within-category

Hope this helps!

-Tracy

• ###### 2. Re: How to count customers with revenues > 10k per year?

Tracy,

What is meant by the 4 rows is one row for each customer category, not the top 4 in each segment. We want to count the number of customers that have at least 10,000 in annual sales and produce a summary report of it, with only 4 rows returned (a summary of the detail). Basically, we want to remove "Customer" from the row and still have the calculations correct.

Kelly

• ###### 3. Re: How to count customers with revenues > 10k per year?

Hi Kelly,

One way this can be done is to turn on the sub-totals as mentioned above, then highlight all of the customers in each segment (but without highlighting the subtotal) , then right click and select Hide. Another option would be to group all the customers in each segment together, select all in each segment, right click and select Group. Then, you can rename them appropriately. Hope this helps a bit!

-Tracy

• ###### 4. Re: How to count customers with revenues > 10k per year?

Tracy,

Thanks for your prompt response. While this is a 1-time fix solution for an issue (and more beneficial for fewer rows of data to combine), do you know of a calculated field that can be created to replicate this concept?

Thanks,

Kelly

• ###### 5. Re: How to count customers with revenues > 10k per year?

Hi Kelly,

One way (although it won't look it's cleanest because there will be overlapping text--even though it's all the same value) is to place Customer on the level of detail shelf. Change the Customers calculation to be the following:

total(countd([Customer]))

When this is placed on the view, right click and select Compute Using-->Customer.

Then, create a similar calculation for Sales.

total(sum(Sales))

And do also compute using Customer. As mentioned it's not ideal, but will provide the results that you are looking for without having to be super manual.

-Tracy

1 of 1 people found this helpful
• ###### 6. Re: How to count customers with revenues > 10k per year?

Tracy -

Thanks so much for your help with this issue. The last response was incredibly helpful, but now the issue of overlapping data remains. Is there any way to eliminate the overlap by another calculation or any other option?

Thanks,

Kelly

• ###### 7. Re: How to count customers with revenues > 10k per year?

Hi Kelly,

I can't think of any way around this, since Customer is needed somewhere on the view to get the correct output.

-Tracy

• ###### 8. Re: How to count customers with revenues > 10k per year?

Hi Kelly,

Here's a way around the overlapping text issue, using the technique Richard Leeke developed at  http://www.clearlyandsimply.com/clearly_and_simply/2011/01/another-look-at-site-catchment-analysis-with-tableau-6-part-3.html.

The technique wraps the TOTAL() formulas like so:

IF FIRST() == 0 THEN

TOTAL(SUM([Sales]))

END

This causes the Total calculation to only return a result for the first row in the partition, and Null for everything else, so in your text table there is only one value to be displayed and no overlapping text. Just like the original TOTAL() calcs, the Compute Using is set to Customer.

Cheers,

Jonathan