Create a table from the source data

[Anonymous]

Hi

Need help in creating a dynamic table out of the employee source data based on a particular column which has names of team leads (includes duplicates). This new table should count the ranks (text)  our each employee of a team lead.

Data Source - This gets updated everyday.

Employee	Region	Country	Team Lead	Rank
Adriana	Europe	UK	Elizabeth	EE+
Albert	Europe	UK	Elizabeth	ME+
Anna	Asia	Philippines	Elizabeth	EE
Blaise	Americas	USA	Geraldin	EE
Caroline	Americas	Canada	Geraldin	EE
Cecilia	Middle East	UAE	Geraldin	NI
Chien	Asia	Greater China	Ingrid	ME
Dorothy	Europe	Ireland	Ingrid	ME+
Edmond	Middle East	UAE	Ingrid	EE
Edwin	Europe	Scotland	Jane	EE+

Team Lead Table

Team Lead	EE Count	EE+ Count
Elizabeth	1	1
Geraldin	2	0
Ingrid	1	0
Jane	0	1

[Fowmy · Accepted solution]

@Anonymous 

Okay, this is what you are looking for I think:

Employee Rank % = 
DIVIDE(
    COUNT(Employee[Employee]),
    CALCULATE(
        COUNT(Employee[Employee]),
        ALLSELECTED(Employee[Rank])
    ) 
)

If you are satisfied with my answer, please mark it as a solution so others can easily find it.

APPRECIATE KUDOS!

[Fowmy]

@Anonymous 

Do you need to have a table created in the format you need or are you trying to show in it in a visual in the same layout?

[Anonymous]

No, thats how my output visual should be, however i need to further calculate the % of the ranks of each TLs based on the employee strength

[Fowmy]

@Anonymous 

Then you simply use a MATRIX visual and plot your fields as I did below.

If you are satisfied with my answer, please mark it as a solution so others can easily find it.

APPRECIATE KUDOS!

[Anonymous]

Hi

Its not quite what i want, although it sort of in the same directio. i want the unique Team Leads in column 1 instead of the employees and rest of the columns to give the % of count of a particular rank divided by the total number of employees of that particular team lead

[Fowmy · Accepted solution]

@Anonymous 

Okay, this is what you are looking for I think:

Employee Rank % = 
DIVIDE(
    COUNT(Employee[Employee]),
    CALCULATE(
        COUNT(Employee[Employee]),
        ALLSELECTED(Employee[Rank])
    ) 
)

If you are satisfied with my answer, please mark it as a solution so others can easily find it.

APPRECIATE KUDOS!

[Anonymous]

this is perfect! how do i convert the values to %?

[Icey · Accepted solution]

Hi @Anonymous ,

Select the column/measure you want to modify, then you will find "%" in menu bar.

Best Regards,

Icey

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

[Anonymous]

Great! Thanks.