Forum Discussion
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 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
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
2 Replies
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
