Forum Discussion
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:
| Contacts | Group 1 | Group 2 | Group 3 | Group 4 |
| Contact 1 | 1 | |||
| Contact 2 | 1 | 1 | 1 | |
| Contact 3 | 1 | 1 | 1 | 1 |
| Contact 4 | 1 | |||
| Contact 5 | 1 | 1 | 1 | |
| Contact 6 | 1 | 1 | 1 | 1 |
| Contact 7 | 1 | |||
| Contact 8 | 1 | 1 | 1 | |
| Contact 9 | 1 | 1 | 1 | |
| Contact 10 | 1 | 1 | 1 | 1 |
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
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.
-----------------------------------------------------
3 Replies
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.
-----------------------------------------------------
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
