SOLVED

Require unique count in pivot or using formula

New Contributor

Hi all,

 

Need a quick help in figuring out the count of unique customers served by a broker.The data I have is across category and hence its possible that one broker is serving the same customer across multiple categories and hence the count in pivot is not unique.I am attaching a sample input and output data.Please suggest how can this be achieved.Thanks!

INPUT:

Broker NameCustomer NameCategory
Broker 1Customer 1Category 1
Broker 1Customer 1Category 2
Broker 1Customer 1Category 3
Broker 1Customer 1Category 4
Broker 2Customer 2Category 1
Broker 3Customer 1Category 1
Broker 3Customer 3Category 2

 

Desired OUTPUT using formula or pivot

 

Broker NameCount of unique customer serviced
Broker 11
Broker 21
Broker 32
3 Replies
Desired output is below
Broker Name>>Count of unique customers served
Broker 1 >1
Broker 2 >1
Broker 3>2
best response confirmed by Vaibhav002 (New Contributor)
Solution

@Vaibhav002 Use Power Pivot where you can specify a Distinct Count.

Riny_van_Eekelen_0-1668683269121.png

 

Thanks @Riny_van_Eekelen..This really helped