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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
PGY
Frequent Visitor

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: 

PGY_0-1663063434770.png

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)

 

PGY_2-1663063632362.png

 

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

PGY_3-1663063790642.png

 

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

1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

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.

1.png2.png

 

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

 

View solution in original post

2 REPLIES 2
PGY
Frequent Visitor

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

tamerj1
Super User
Super User

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.

1.png2.png

 

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

 

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!