MAX Count by group extended to all groups

[Anonymous]

I'm creating a report that requires each scrum team be placed in a column listing the persons in a roles row. I need to keep the rows aligned by role. The numbers of roles on each team may vary. So I need to create a table that I can do a right join with to ensure missing rows are replaced by blanks. I know how to do the report I just need help with the joining table.

I have this Data Table:

team_id                           role_id

Scrum_Team_1	Developer
Scrum_Team_1	Developer
Scrum_Team_1	Tester
Scrum_Team_2	Developer
Scrum_Team_2	Program_Manager
Scrum_Team_3	Developer
Scrum_Team_3	Developer
Scrum_Team_3	Developer
Scrum_Team_3	Tester
Scrum_Team_3	Program_Manager
Scrum_Team_3	Program_Manager

and the needed table would be this:

team_role_count

Scrum_Team_1Developer1

Scrum_Team_1Developer2

Scrum_Team_1Developer3

Scrum_Team_1Tester1

Scrum_Team_1Program_Manager1

Scrum_Team_1Program_Manager2

Scrum_Team_2Developer1

Scrum_Team_2Developer2

Scrum_Team_2Developer3

Scrum_Team_2Tester1

Scrum_Team_2Program_Manager1

Scrum_Team_2Program_Manager2

Scrum_Team_3Developer1

Scrum_Team_3Developer2

Scrum_Team_3Developer3

Scrum_Team_3Tester1

Scrum_Team_3Program_Manager1

Scrum_Team_3Program_Manager2

The Report would be

Roles	Scrum Team 1	Scrum Team 2	Scrum Team 3
Developer	John	Peter	Sulley
Developer	Sally		Ron
Developer			Joe
Tester	Brian		Harry
Program_Manager		Larry	Willy
Program_Manager			Mathew

Thanks for the help!

[v-eachen-msft · Accepted solution]

Hi @Anonymous ,

You need to add an index column with Query Editor firstly.

Then create a column “Value”:

Value =
RANKX (
    FILTER (
        'Table',
        EARLIER ( 'Table'[role_id] ) = 'Table'[role_id]
            && EARLIER ( 'Table'[team_id] ) = 'Table'[team_id]
    ),
    'Table'[role_id],
    ,
)

Create an accumulate column “Count”:

Count =
CALCULATE (
    SUM ( 'Table'[Value] ),
    FILTER (
        ALLEXCEPT ( 'Table', 'Table'[role_id] ),
        'Table'[role_id] = EARLIER ( 'Table'[role_id] )
            && 'Table'[team_id] = EARLIER ( 'Table'[team_id] )
            && 'Table'[index] <= EARLIER ( 'Table'[index] )
    )
)

Combine role_id and Count:

RoleColumn =
IF (
    NOT ( ISBLANK ( 'Table'[Count] ) ),
    'Table'[role_id] & 'Table'[Count],
    BLANK ()
)

Create a new table by team_id and RoleColumn.

TempTable =
VAR temp1 =
    DISTINCT ( 'Table'[team_id] )
VAR temp2 =
    CALCULATETABLE (
        VALUES ( 'Table'[RoleColumn] ),
        FILTER ( 'Table', 'Table'[RoleColumn] <> BLANK () )
    )
RETURN
    CROSSJOIN ( temp1, temp2 )

Now, you can get team_role_count by combining  team_id with RoleColumn:

team_role_count =
TempTable[team_id] & TempTable[RoleColumn]

Here is the final table.

Best Regards,

Eads

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.