Log In
Register
Home
Power Query
July 11, 2016
Offline
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
Trusted Members
Moderators
November 1, 2018
Offline
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)))
Power Query
July 11, 2016
Offline
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
Trusted Members
Moderators
November 1, 2018
Offline
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. 🙂
1 Guest(s)
