Forum Discussion

NuTek's avatar
NuTek
Frequent Visitor
9 years ago
Solved

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    ...
Need Help
  • v-ljerr-msft's avatar
    v-ljerr-msft
    9 years ago

    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.:smileyhappy:

    % 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 )

    Regards

Sean's avatar
Sean
Community Champion
9 years ago

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

 

If so this Measure should do it... :smileyhappy:

 

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

NuTek's avatar
NuTek
Frequent Visitor
9 years ago

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!

 

 

  • Sean's avatar
    Sean
    Community Champion
    9 years ago

    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! :smileyhappy:

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Microsoft Employee
    9 years ago

    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?:smileyhappy:

    % 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 )

    Regards

    • NuTek's avatar
      NuTek
      Frequent Visitor
      9 years ago

      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's avatar
        v-ljerr-msft
        Microsoft Employee
        9 years ago

        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.:smileyhappy:

        % 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 )

        Regards