DIVIDE DISTINCT COUNT WITH FILTER

[Sachet_716]

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

[benjudechan]

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!

[ChandeepChhabra]

@benjudechan You can download the solution from here

[ChandeepChhabra]

@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

[Sachet_716]

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? 

https://drive.google.com/open?id=1rM5IAqUWDN0Disei1mtzZzhHFnPfCVR9

Rgds, Sachet

[ChandeepChhabra · 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)

[Sachet_716]

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

Rgds, sachet

[v-yuezhe-msft]

@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

[Sachet_716]

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

[v-yuezhe-msft]

@Sachet_716,

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

Regards,

Lydia

[Sachet_716]

Hi Lydia, 

I uploaded dummy pbix file here: 

https://drive.google.com/open?id=1rM5IAqUWDN0Disei1mtzZzhHFnPfCVR9

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

Rgds, Sachet