Dynamic Summarized table - selected count

[PGY]

Dear experts,

Need help to create a dynamic summarized table that achieve the following outcome.

The input table contains team member and priority for the role (where 1 is the highest). One member can be considered for several roles with different priority. Would like to calculate distinct count of member only at their most priority level. The summary table should be dynamically adjusted the calculation based on the user selections on filters (Organization, Team and Role).

Input Table: 

Expected outcome: 

Case 1) if user doesn't select any filter (means all population)

Result:

Priority 1 has 2 counts for C1 and C2

Priority 2 has 1 count for C3  (C2 is already counted in priority 1 therefore not included here)

Priority 3 has 1 count for C4 (C1 and C3 are already counted in higher priority, therefore not included here)

Case 2) User select filter Organization = O1

Result:

Priority 1 has 2 counts for C1 and C2

Priority 2 has 1 count for C3 (C2 is already counted for priority 1 therefore not included here)

Priority 3 has 0 count as C1 is already counted for priority 1 therefore not included here

I tried summarized table however it is static - not adjusting to the user filters. Kindly advice on how to achieve this.

Many thanks in advance.

Regards,

Peggy

[PGY]

@tamerj1 , Awesome - thank you so much for the solution!

[tamerj1 · Accepted solution]

Hi @PGY 
Please follow the method described in the attached file. THe solution is based on the assumption that Priority column is of integer data type and contains consecutive values like { 1, 2, 3, ..... }

You will need to have a separate and disconnected table for slicing that contains the priority name and number.

Distinct Count = 
SUMX ( 
    VALUES ( Priority[Priority] ),
    VAR CurrentPriority = Priority[Priority]
    VAR PreviousPriorities = FILTER ( ALL ( Priority[Priority] ), Priority[Priority] < CurrentPriority )
    VAR CurrentMembers = CALCULATETABLE ( VALUES ( Input[Member] ), Input[Priority] = CurrentPriority )
    VAR PreviousMembers = CALCULATETABLE ( VALUES ( Input[Member] ), Input[Priority] IN PreviousPriorities )
    RETURN
        COUNTROWS ( EXCEPT ( CurrentMembers, PreviousMembers ) ) + 0
)

% Local = 
VAR Numerator = CALCULATE ( [Distinct Count], Input[Origion] = "Local" )
VAR Denomimator = [Distinct Count]
RETURN
    DIVIDE ( Numerator, Denomimator ) + 0

*UPDATE

The solution is updated fixing the Total