Forum Discussion

Sara12957's avatar
Sara12957
New Member
3 years ago
Solved

Calculating % Overlap

Hello, I am quite new to Power BI and I need support to create a card that shows the % overlap between two groups.  I have a list of contacts and I need to know which ones were contacted by both ...
Need Help
  • Mikelytics's avatar
    Mikelytics
    3 years ago

    Hi Sara12957 

     

    Please try the following

     

    my base table (Contacts)

     

    the overlap measure

     

    Overlap = 

var var_TotalContacts = 
   CALCULATE(
      DISTINCTCOUNT(Contacts[Contacts]),
      ALL(Contacts[Contacts])
   )

var var_ContactsGroup1andGroup2 = 
   SUMX(
      ALL(Contacts[Contacts]),
      var CountGroup1 = CALCULATE(COUNT(Contacts[Value]),Contacts[Group]="Group 1")
      var CountGroup2 = CALCULATE(COUNT(Contacts[Value]),Contacts[Group]="Group 2")

    RETURN
    IF(CountGroup1>0 && CountGroup2 > 0 , 1, 0)

   )

RETURN
DIVIDE(var_ContactsGroup1andGroup2,var_TotalContacts)

     

    The result

    it could be that you need to use instead of ALL() the functions ALLSELECTED() or VALUES()

    Furthermore it coule be that you have to replace COUNT(Contacts[Value]) by the measures which you created with 0 and 1 as a result but the filter on the group should stay.

     

    Best regards

    Michael

    -----------------------------------------------------

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your thumbs up!

    @ me in replies or I'll lose your thread.

    -----------------------------------------------------

    LinkedIn

     

     

     

amitchandak's avatar
amitchandak
Super User
3 years ago

Sara12957 ,  Three measures over between g1 and g2

G1 = sum(Table[Group1])

 

G2 = sum(Table[Group1])

 

Over lap % = divide( countx(filter(values(Table[Contact]), not(isblank([G1])) && not(isblank([G1])) ), [Contact] ), count(Table[Contact]))

Sara12957's avatar
Sara12957
New Member
3 years ago

Thank you for you response. Unfortunately the solution provided is not working for me. I might have not explained the situation properly.

The groups are part of the same column in the dataset (I am interested only in G1 ans G2) and the values are not simply a count of the column "contact" but there is another measure which is either 1 or 0. I need to exclude the cases where the measure is zero for either group 1 or group 2 and calculate the overlap %.

Let me know if it is clear now.

Thanks