Need help in adding a measure

[Anonymous]

Hi

I have a matrix visual included in my dashboard like shown below in the table. It basically tells for each TLs, the teams Met the target or Not. The fact table contains employee level data and the measure i have added to get the below table is =If([EmployeeCount]=0,"",if([TargetMetCount]/[EmployeeCount]>10%,"Met","Not Met")). Now i need the count of teams (sales) for each TLs who are "Met" - like show below in the Desired Outcome table.

TL	Sales 1	Sales 2	Sales 3	Sales 4	Sales 5	Sales 6	Sales 7
Bharath		Met	Not Met	Not Met	Met
Nikhil	Not Met	Met	Met	Met
Abhishek		Met	Met	Met	Not Met	Not Met
Ashok					Met	Met	Not Met
Girish		Not Met	Met
Rajiv	Not Met			Met	Met	Met	Not Met
Aamir			Met	Not Met
Sushma	Not Met		Met
Rupika		Not Met	Met
Madhu		Met			Not Met
Sreeja	Not Met		Met

TL	Desired Outcome
Bharath	2
Nikhil	3
Abhishek	3
Ashok	2
Girish	1
Rajiv	3
Aamir	1
Sushma	1
Rupika	1
Madhu	1
Sreeja	1

[Anonymous · Accepted solution]

@Anonymous 

You should unpivot the table to make the dax calculations easy. 

1. Go to query editor, hold on Ctrl, select Sale1-7 columns, and unpivot.

2. Close and apply, create the following count measure. 

Measure = CALCULATE(COUNTROWS('Table'),FILTER('Table',[Value]="Met"),ALLEXCEPT('Table','Table'[TL]))

Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[az38]

Hi @Anonymous 

try this

Measure = 
CALCULATE(SUMX(Table, IF([TL Sales Measure] = "Met", 1, 0)), ALLEXCEPT(Table, Table[TL]) )

[Anonymous]

Hi

Thanks, but this is giving me the total number of employees. but i need the number of teams for each TL who have achieved the target.

[amitchandak]

@Anonymous , something like this

if(calculate([EmployeeCount], allexcept(Table, Table[TL])) =0,"",calculate(divide([TargetMetCount],[EmployeeCount]), allexcept(Table, Table[TL]))>10%,"Met","Not Met")

[Anonymous]

Hi

Am not sure if this is counting the number of teams with "Met" for each TL, am getting syntax error occured during parsing.