Hi,
I have a table that contains sales data. The table has various fields that users use to filter the data. The first column in the table is the customer account number. This is a text field. There are multiple rows of data for each customer.
When a user filters the table I want to show the unique number of customers that the filtered data relates to. How do you count the customer account field to show the unique number of values?
Thanks
Bax
You could try something like:
=COUNTA(UNIQUE(FILTER(Table1[Column1],SUBTOTAL(103,OFFSET(Table1[Column11],ROW(Table1[Column1])-MIN(ROW(Table1[Column1])),0,1,1))=1)))
Hi Velouria,
Thanks for the feedback I will give it a try. I did actually find another way that works for me. I first of all added a column to my table which indicates if the row is visible when the table is filtered. I called the column "Visible". The formula, =SUBTOTAL(3,[@Customer]), points to the Customer field. It returns 1 if the row is visible or 0 if not.
I then added the following formula to count the number of customers:
=COUNTA(UNIQUE(FILTER(Table_Sales[Customer],Table_Sales[Visible])))
If the table is then filtered on any of the other fields it counts the unique number of customers displayed.
I have attached an example file to illustrate this.
Cheers
Bax
That's exactly the approach the formula I suggested uses, but without the helper column. If the helper column is OK, it's probably a better option. 🙂
