Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Sachet_716
Helper I
Helper I

DIVIDE DISTINCT COUNT WITH FILTER

I need help please...

 

I have this measure:

     Measure % = DISTINCTCOUNT (Sales [CustomerCode]) / DISTINCTCOUNT (CustomerList[CustomerCode])  

 

In my Sales table, I have different brands and I want to measure the precentage of cutsomers who purchased Brand 1 to overall customers from the Customer list Table. My problem is, whenever i filter Brand (which I need for this DISTINCTCOUNT (Sales [CustomerCode]), my CustomerList is also filtered here DISTINCTCOUNT (CustomerList[CustomerCode]) .

 

My objective is to divide distinct no.of cutsomers from Sales Table to the OVERALL number of customers in CustomerList. 

 

Example: No. of Customers in Sales Table (250) / No. of Customers in my CutsomerList Table (300)   - I am getting this already

               No. of Customers for Brand 1 (97) / No. of Customers in my CutsomerList Table (300)     - This is where my issue is  😞

 

Thank you guys in advance! 

 

Rgds, Sachet

1 ACCEPTED SOLUTION

Hi @Sachet_716

 

Lidya @v-yuezhe-msft is correct here. You need to have all possible codes in the customer table.

 

Just in case if you still want to make this work. Do the following

 

Step 1 - Delete the relationship between Sales and Customer Tables

 

Step 2 - Add a following measure to the Customer table 

Total Customer Country Wise = CALCULATE(DISTINCTCOUNT(CUSTOMER[CUSTOMER CODE]),ALLSELECTED(CUSTOMER[COUNTRY]))

 

Step 3 : Update your Coverage % formula to

Coverage% = DIVIDE (DISTINCTCOUNT('SALES DATA'[Customer Code]) ,[Total Customer Country Wise])

 

Step 4: Insert a slicer on Country from Customer Table and NOT the Sales Table

 

I am supposing it should then work!

 

Sending you 2 files to look over -

With Relationships and Corrected Data

Without Relationships and (As is Data)

 

 

View solution in original post

10 REPLIES 10

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.