Forum Discussion

Sachet_716's avatar
Sachet_716
Icon for Helper I rankHelper I
8 years ago
Solved

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...
Need Help
  • ChandeepChhabra's avatar
    ChandeepChhabra
    8 years ago

    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)

     

     

benjudechan's avatar
benjudechan
New Member
8 years ago

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!