Forum Discussion
Need help in adding a measure
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
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.
5 Replies
- az38
Community Champion
Hi Anonymous
try this
Measure = CALCULATE(SUMX(Table, IF([TL Sales Measure] = "Met", 1, 0)), ALLEXCEPT(Table, Table[TL]) )- amitchandak
Super User
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
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.
