Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Sara12957
New Member

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 group 1 and 2 .

The structure is similar to the following:

ContactsGroup 1Group 2Group 3Group 4
Contact 11   
Contact 2 111
Contact 31111
Contact 41   
Contact 5 111
Contact 61111
Contact 71   
Contact 8 111
Contact 9 111
Contact 101111

 

So I'd like to have a card that displays the % overlap between group 1 and 2 only, that is in this case 30%.
I managed to create a table that shows the list of contacts belonging to the first 2 groups by using some filters (group filter and measure >1) but then I am unable to convert it to a card.

Can anyone help me?

Thank you very much


 

1 ACCEPTED SOLUTION
Mikelytics
Resident Rockstar
Resident Rockstar

Hi @Sara12957 

 

Please try the following

 

my base table (Contacts)

Mikelytics_0-1669898036828.png

Mikelytics_1-1669898050789.png

 

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

Mikelytics_4-1669898123514.png

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

 

 

 

------------------------------------------------------------------
Visit my blog datenhungrig which I recently started with content about business intelligence and Power BI in German and English or follow me on LinkedIn!

View solution in original post

3 REPLIES 3
Mikelytics
Resident Rockstar
Resident Rockstar

Hi @Sara12957 

 

Please try the following

 

my base table (Contacts)

Mikelytics_0-1669898036828.png

Mikelytics_1-1669898050789.png

 

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

Mikelytics_4-1669898123514.png

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

 

 

 

------------------------------------------------------------------
Visit my blog datenhungrig which I recently started with content about business intelligence and Power BI in German and English or follow me on LinkedIn!
amitchandak
Super User
Super User

@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]))

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.