4 Replies Latest reply on Aug 29, 2016 8:32 AM by Benjamin Greene

# Counting amount of invoice per customer

Hi to all Tableau experts :-)

I have a problem which sounds very simple but I'm not able to solve it.
I tried to count the amount of invoices for each customer.
My worksheet has the following structure:
column 1: customer
column 2: invoice number

column 3: revenues were only showed to check my filter conditions

Filter: Only shows the value "1" of a calculated field which means the revenue of invoice is <400

Example:

My aim is to get this view

If I use the cound() function to count invoices per customer the results are wrong. It seems that Tableau always sum up the revenues and if
the value is over 400 in total for each customer the whole dataset will be kicked out.

I also tried to use my flag of the filter invoice is <400 in order to get a sum for each customer but it didn't work for me.

Anyone ideas?

• ###### 1. Re: Counting amount of invoice per customer

I forgot to mention that the revenues are the results of a calculation.

• ###### 2. Re: Counting amount of invoice per customer

Is the Revenue calculation an aggregate calculation? If so, in your Filter calculation, you could try something like IF {INCLUDE [Customer], [Invoice] : [Revenue]}<400 THEN 1 ELSE 0 END. This way, it would only perform that calculation on each invoice individually, rather than for all the invoices for a customer.

If your Revenue calculation is not an aggregate calculation already, then my guess is that your Filter calculation has a SUM([Revenue]) in it right now. If this is the case, just try removing the SUM part of it.

• ###### 3. Re: Counting amount of invoice per customer

Yes the revenue calculation is already an aggregate calculation.

I tried it with your include condition but there is still the same behaviour.

• ###### 4. Re: Counting amount of invoice per customer

I went ahead and mocked up your question in a sample dataset and came up with a solution. That is on sheet 3. Sheets 1 and 2 just show all the underlying data so that you can check to make sure the numbers are right. The only real difference is that I should have written FIXED instead of INCLUDE.