Forum Discussion

PGY's avatar
PGY
Frequent Visitor
3 years ago
Solved

Dynamic Summarized table - selected count

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 m...
  • tamerj1's avatar
    3 years ago

    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