Forum Discussion
Helper IDIVIDE 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
Hi Sachet_716
Lidya Anonymous 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
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_716Hi 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
- ChandeepChhabra
Impactful Individual
Here is what I could think of as a solution. You can download the Excel (powerpivot) workbook and implement the same in PBI
- I created 2 dummy tables -> Sales (with columns - Date, Brand, Customer Name, Unit Sales) and Customer (with just Customer Name column)
- 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
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
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/7for 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
