Forum Discussion
Caluclate unique customer count
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
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~
3 Replies
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~
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.
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 :
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
