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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
NuTek
Frequent 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

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

View solution in original post

6 REPLIES 6
Sean
Community Champion
Community Champion

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
Frequent Visitor

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!

 

 

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

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?

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

Sean
Community Champion
Community Champion

@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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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