Caluclate unique customer count

[Ramgopal_PBI]

Hi,

A customer is purchasing products from multiple distributors. if  morethan 80%  value is purchased from one distributor then the customer is unique to that distributor. As shown in the below table1, A customer C9499 purchase products from four distributors but unique to the distributor D62. 

Table1:

Distributor	Customer	Total Sales	Sales%	Unique customer
D19	C9499	$1,500	3.43%	0
D51	C9499	$2,982	6.81%	0
D62	C9499	$38,860	88.79%	1
D91	C9499	$426	0.97%	0
Total		$43,768	100%	1

how to caluclate number of unique customers for each distributor like below table2 using DAX in PowerBI  with dynamic filter selection ?  

Distributor	Total No of Customers	No of Unique Customers
D19	1314	949
D51	1286	953
D62	1321	966
D91	1236	918
Grand Total	5157	3786

Data has following fields 

Distributor ,

Region ,

Country,

Quarter,

Product ,

Customer ,

Units ,

Unit Price ,

Total Sales

Thanks,

Ram

[xifeng_L · Accepted solution]

Hi @Ramgopal_PBI ,

You can try below measure.

No of Unique Customers = 
COUNTROWS(
    FILTER(
        ALLSELECTED('Distributor Sales'[Customer]),
        CALCULATE(
            DIVIDE(
                SUM('Distributor Sales'[Total Sales]),
                CALCULATE(
                    SUM('Distributor Sales'[Total Sales]),
                    ALLSELECTED('Distributor Sales'[Distributor])
                )
            )
        )>0.8
    )
)

Please mark this as solution and give Kudos, if this helps resolve your issue.

Thank you~

[v-yifanw-msft]

Hi @Ramgopal_PBI ,

Based on the information you provided, can you explain how your "unique customers" was calculated to get it. If you do not mind could you also provide some detailed data (without sensitive information), providing specific data and clearly expressing the results you want can help us better understand your problem.

How to Get Your Question Answered Quickly - Microsoft Fabric Community

If it does not help, please provide more details with your desired out put and pbix file without privacy information.

Best Regards,

Ada Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ramgopal_PBI]

Hi Ada Wang,

We have 100 distributors and 10000 customers in the sample data. A customuer bought products from the multiple distributors , if the customer bought mostly (above 80%) from one distributor then the customer is unique. Let say If the customer 'C' bought total 100$ from three distributors  D1,D2 and D3 10$,7$ and 83$ respectively.Here the customer 'C' is bought morethan 80% value from the distributor D3 , hence the customer 'C' is unique. 

Sales% bought from D1 = 10/(10+7+83)

Sales% bought from D2 = 7/(10+7+83)

Sales% bought from D3 = 83/(10+7+83)

Now need to caluclate the number of unique customers by Distributor.

The required output is  

Distributor	Total No of Customers	No of Unique Customers
D19	1314	949
D51	1286	953
D62	1321	966
D91	1236	918
Grand Total	5157	3786

Sample Data file : 

https://docs.google.com/spreadsheets/d/1jJnXYD5bA6tMzsdQctbiosBFfjVhte-r/edit?usp=drive_link&ouid=11...

PBIX File: 

https://drive.google.com/file/d/11BnGBM1aFVas4ewdaK8caJNS6_WoiETg/view?usp=drive_link

This might be achived through nested aggregations.

Step 1 : Distributor , Customer, Sales%

Step2: Distributor,Customer count where Sales% >80%

Thank you,

Ram