cancel
Showing results 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

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:

 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

1 ACCEPTED SOLUTION
Resident Rockstar

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!

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

------------------------------------------------------------------
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!
3 REPLIES 3
Resident Rockstar

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!

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

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

New Member

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

Announcements

#### Power BI Monthly Update - July 2024

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

#### Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors