cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Impactful Individual

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)

10 REPLIES 10
New Member

Hi Power BI Community,

I'm new to Power BI and need some help creating a measure that will perform the following calculation:

For example:

for crash 1 and script A, there is a total of 5 counts .  However I'm only interested in the distinct session id for crash 1 and script A which is 4 the numerator.  The denominator is all distinct count for script A which is 7.  The occurence % would be 4/7

for crash 2 and script A, the % would be 3/7

for crash 3 and script B, the % would be  2/6

for crash 4 and script B, the % would be 3/6

for crash 5 and script B, the % would be 1/6

How can i get a measure that perform this calculation with it being dynamic when slicing by "Script"?  Any help is much appreciated!

Impactful Individual
Impactful Individual

@Sachet_716

Here is what I could think of as a solution. You can download the Excel (powerpivot) workbook and implement the same in PBI

1. I created 2 dummy tables -> Sales (with columns - Date, Brand, Customer Name, Unit Sales) and Customer (with just Customer Name column)
2. Assuming that customer table will only have unique names, I created a relationship between both tables

Then I wrote the following measure

```Distinct as % of Total Customers
=DISTINCTCOUNT(Sales[Customer])/COUNTROWS(ALL(Customer))```

The resulting pivot (with brand on rows and measure in values) looks like this

Helper I

Hi Chandeep,

Thank you for the reposnse, however, its giving me the same output whenever i apply some filters on the slicer. Can you check my pbix file below? I placed there my expected result, or perhaps, it is not doable?

Rgds, Sachet

Impactful Individual

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)

Helper I

Thank you Chandeep, I tried this and this works. 🙂

Rgds, sachet

Microsoft Employee

@Sachet_716,

Could you please re-check your data? I note that you create relationship between Cutsomer and sales data table using customer code field, but the customer code of Customer table doesn't contain all the possible customer codes in sales data table.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Helper I

Hi Lydia,

Yes, not all customers are included in the customer data list because we are billing some customers whihc are outside the specified journey plan, thus, I am expecting that there will be >100% on the answers.  Is it wrong?

Rgds, Sachet

Microsoft Employee

@Sachet_716,

Please share dummy data of your Sales and CustomerList tables and post expected result based on the dummy data here.

Regards,

Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Helper I

Hi Lydia,

I uploaded dummy pbix file here:

Thank you so much. I am really new with PBI and DAX. 😞

Rgds, Sachet

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.