Forum Discussion

andybamber's avatar
andybamber
Helper III
4 years ago

Measure Help

Hello Community!   Looking for some help if possible. I have attached a PBIX with a sample file.   I have a table of data with customer id (unique) and for each customer id there is a cat name an...
Help Requested
Measures
ribisht17's avatar
ribisht17
Super User
4 years ago

andybamber 

 

Duplicate Cat Codes = if( CALCULATE(COUNT('Table'[Cust_ID]),ALLEXCEPT('Table','Table'[Cat_Name]))>1,1,0)

 

Duplicate Cat Count = CALCULATE(DISTINCTCOUNT('Table'[Cat_Name]),FILTER(all('Table'),[Duplicate Cat Codes]=1))
 
Total Distinct Count of Cats = CALCULATE(DISTINCTCOUNT('Table'[Cat_Name]),all('Table'))
 
Solution = DIVIDE([Duplicate Cat Count],'Table'[Total Distinct Count of Cats])
 
Ans will be 3/7 
 

Regards,

Ritesh

Mark my post as a solution if it helped you| Munde and Kudis (Ladies and Gentlemen) I like your Kudos!! !!
My YT Channel Dancing With Data !! Connect on Linkedin !!Power BI for Tableau Users 

andybamber's avatar
andybamber
Helper III
4 years ago

Hi ribisht17  where does the [Duplicate Cat Codes] from?

 

Cheers

Andy

  • ribisht17's avatar
    ribisht17
    Super User
    4 years ago

    Sorry to miss that,

     

    STEP 1

    Duplicate Cat Codes = if( CALCULATE(COUNT('Table'[Cust_ID]),ALLEXCEPT('Table','Table'[Cat_Name]))>1,1,0)

    STEP 2

    Duplicate Cat Count = CALCULATE(DISTINCTCOUNT('Table'[Cat_Name]),FILTER(all('Table'),[Duplicate Cat Codes]=1))
    STEP 3
    Total Distinct Count of Cats = CALCULATE(DISTINCTCOUNT('Table'[Cat_Name]),all('Table'))
    STEP 4
    Solution = DIVIDE([Duplicate Cat Count],'Table'[Total Distinct Count of Cats])
     
    Mark my post as a solution if it helped you| Munde and Kudis (Ladies and Gentlemen) I like your Kudos!! !!
    My YT Channel Dancing With Data !! Connect on Linkedin !!Power BI for Tableau Users