Forum Discussion

Ramgopal_PBI's avatar
Ramgopal_PBI
Frequent Visitor
2 years ago
Solved

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 tab...
DAX
  • xifeng_L's avatar
    xifeng_L
    2 years ago

    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~

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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's avatar
Ramgopal_PBI
Frequent Visitor
2 years ago

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  

DistributorTotal No of CustomersNo of Unique Customers
D191314949
D511286953
D621321966
D911236918
Grand Total51573786

 

Sample Data file : 

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

 

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