cancel
Showing results for
Did you mean:
NuTek Regular Visitor

## Calculating overlap between selected groups

Given the following table and data:

=====     ====
XYZ        A
XYZ        C
XYZ        D
XYZ        E
PDQ        B
PDQ        C
PDQ        D
PDQ        E
LUV        G
LUV        H

I'd like to create a Matrix showing the count of the overlap of two selected groups (XYZ, PDQ) assigned to the same task:

Group   Group
A        1       0          1
B        0       1          1
C        1       1          2
D        1       1          2
E        1       1          2
------------------ ===========
Overlap: 3

And then have a card (or other visual) displaying the Percent overlap between the two selected groups:

Group XYZ:   75%       ( 3 overlapping out of 4)

Is this possible in Power BI?
In Excel I can just do a CountIf() on the Overlap column, but I'm not sure how to achieve this in Power BI

1 ACCEPTED SOLUTION

Accepted Solutions
v-ljerr-msft Super Contributor

## Re: Calculating overlap between selected groups

Hi @NuTek,

You can use FIRSTNONBLANK/LASTNONBLANK function to get the first/last selected group.

`First Selected Group = FIRSTNONBLANK(Table1[Group],1)`

The formula below is for your reference. ```% Overlap for first Selected Group =
VAR firstSelectedGroup =
FIRSTNONBLANK ( Table1[Group], 1 )
VAR count_of_overlap =
CALCULATE (
FILTER (
Table1,
CALCULATE ( COUNT ( Table1[Task] ), ALLSELECTED ( Table1[Group] ) ) = 2
)
)
+ 0
CALCULATE (
'Table1'[Group] = firstSelectedGroup
)
+ 0
RETURN
DIVIDE ( count_of_overlap, count_of_task_XYZ, 0 )``` Regards

6 REPLIES 6 Super User

## Re: Calculating overlap between selected groups

Shouldn't your example give 3/5 or 60% ?

If so this Measure should do it... ```% Overlap =
DIVIDE (
CALCULATE (
DISTINCTCOUNT ( 'Table'[Group] ),
),
0
)``` NuTek Regular Visitor

## Re: Calculating overlap between selected groups

Thanks for the fast reply The number I'm looking for is the number of "2"'s in the "Overlap" column divided by the number of rows with a "1" in the Group columns.   ex: Group XYZ has 4 tasks (A,C,D,E) with 3 tasks appearing in both Group XYZ and PDQ (there are 3 columns in Overlap  with a "2")

Will try yo play with the solution you provided.

Thanks! Super User

## Re: Calculating overlap between selected groups

@NuTek

You can always hardcode XYZ in the formula

```% Overlap for XYZ =
DIVIDE (
CALCULATE (
DISTINCTCOUNT ( 'Table'[Group] ),
),
CALCULATE ( DISTINCTCOUNT ( 'Table'[Task] ), 'Table'[Group]="XYZ" ),
0
)```

Hope this helps! v-ljerr-msft Super Contributor

## Re: Calculating overlap between selected groups

Hi @NuTek,

Could you try using the formula below to create a measure, then just show the measure on the Card visual to see if it works? ```% Overlap for XYZ =
VAR count_of_overlap =
CALCULATE (
FILTER (
Table1,
CALCULATE ( COUNT ( Table1[Task] ), ALLSELECTED ( Table1[Group] ) ) = 2
)
) + 0
CALCULATE ( DISTINCTCOUNT ( 'Table1'[Task] ), 'Table1'[Group] = "XYZ" ) + 0
RETURN
DIVIDE ( count_of_overlap, count_of_task_XYZ, 0 )```  Regards

NuTek Regular Visitor

## Re: Calculating overlap between selected groups

Thanks for the formula.  The results look goodin your example...  Is there a way to not have to hardcode the "XYZ" and replace that with the selected value?

v-ljerr-msft Super Contributor

## Re: Calculating overlap between selected groups

Hi @NuTek,

You can use FIRSTNONBLANK/LASTNONBLANK function to get the first/last selected group.

`First Selected Group = FIRSTNONBLANK(Table1[Group],1)`

The formula below is for your reference. ```% Overlap for first Selected Group =
VAR firstSelectedGroup =
FIRSTNONBLANK ( Table1[Group], 1 )
VAR count_of_overlap =
CALCULATE (
FILTER (
Table1,
CALCULATE ( COUNT ( Table1[Task] ), ALLSELECTED ( Table1[Group] ) ) = 2
)
)
+ 0 