cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
NuTek Regular Visitor
Regular Visitor

Calculating overlap between selected groups

Given the following table and data:

 

Group     Task
=====     ====
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
Task    XYZ     PDQ      Overlap
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
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.Smiley Happy

% Overlap for first Selected Group =
VAR firstSelectedGroup =
    FIRSTNONBLANK ( Table1[Group], 1 )
VAR count_of_overlap =
    CALCULATE (
        DISTINCTCOUNT ( Table1[Task] ),
        FILTER (
            Table1,
            CALCULATE ( COUNT ( Table1[Task] ), ALLSELECTED ( Table1[Group] ) ) = 2
        )
    )
        + 0
VAR count_of_task_XYZ =
    CALCULATE (
        DISTINCTCOUNT ( 'Table1'[Task] ),
        'Table1'[Group] = firstSelectedGroup
    )
        + 0
RETURN
    DIVIDE ( count_of_overlap, count_of_task_XYZ, 0 )

r2.PNG

Regards

6 REPLIES 6
Super User
Super User

Re: Calculating overlap between selected groups

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

 

If so this Measure should do it... Smiley Happy

 

% Overlap =
DIVIDE (
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Group] ),
        ALLEXCEPT ( 'Table', 'Table'[Task] )
    ),
    DISTINCTCOUNT ( 'Table'[Task] ),
    0
)

% Overlap.png

NuTek Regular Visitor
Regular Visitor

Re: Calculating overlap between selected groups

Thanks for the fast reply Smiley Happy

 

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
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] ),
        ALLEXCEPT ( 'Table', 'Table'[Task] )
    ),
    CALCULATE ( DISTINCTCOUNT ( 'Table'[Task] ), 'Table'[Group]="XYZ" ),
    0
)

Hope this helps! Smiley Happy

v-ljerr-msft Super Contributor
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?Smiley Happy

% Overlap for XYZ = 
VAR count_of_overlap =
    CALCULATE (
        DISTINCTCOUNT ( Table1[Task] ),
        FILTER (
            Table1,
            CALCULATE ( COUNT ( Table1[Task] ), ALLSELECTED ( Table1[Group] ) ) = 2
        )
    ) + 0
VAR count_of_task_XYZ =
    CALCULATE ( DISTINCTCOUNT ( 'Table1'[Task] ), 'Table1'[Group] = "XYZ" ) + 0
RETURN
    DIVIDE ( count_of_overlap, count_of_task_XYZ, 0 )

r4.PNG

r5.PNG

Regards

NuTek Regular Visitor
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
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.Smiley Happy

% Overlap for first Selected Group =
VAR firstSelectedGroup =
    FIRSTNONBLANK ( Table1[Group], 1 )
VAR count_of_overlap =
    CALCULATE (
        DISTINCTCOUNT ( Table1[Task] ),
        FILTER (
            Table1,
            CALCULATE ( COUNT ( Table1[Task] ), ALLSELECTED ( Table1[Group] ) ) = 2
        )
    )
        + 0
VAR count_of_task_XYZ =
    CALCULATE (
        DISTINCTCOUNT ( 'Table1'[Task] ),
        'Table1'[Group] = firstSelectedGroup
    )
        + 0
RETURN
    DIVIDE ( count_of_overlap, count_of_task_XYZ, 0 )

r2.PNG

Regards