Looping through a table and calculating values, then summing up those values

[powerbieber]

Hi,

What I need to do is loop through all employees and check the leads they brought for a whole year. If an employee has more than 10 leads, he will be on target. If he has leass he will not be on target. Then I want to find how many weeks an employee was on target. 

I'm now doing it for the whole year using IF. This is what I have:

onTarget = CALCULATE(IF(COUNTA('Lead'[Status])>=10,1,0))

It's giving me a 1 for employees who have more than 10 leads and a 0 for those who have less than 10. However, It is doing it for the whole year. How do I make it check this condition for 52 weeks, assign a 1 or a 0 for each week, and then give me the total?

Any help would be appreciated,

[v-yuezhe-msft]

@powerbieber,

Could you please share dummy data of your table and post the expected result based on the dummy data here?

Regards,
Lydia

[powerbieber]

There is a leads table that lists the details of every lead, including which employee it was done by:

leadID      employee ID         date

158912            0347           11/9/17

159813            0412           11/10/17

159814            0231           11/10/17

159815            0412           11/10/17

......

There is also an employee table that lists the IDs and names of all employees:

employeeID      employeeName

       0231                Christie

       0347                Kevin

       0412                Jenny

I want to add a column to the employee table the whole year, counts how many leads each employee had in every week, and gives me the total of weeks in which each employee had 5 leads or more.

There will be a calculated column or measure with the name "weeksOnTarget" added to the employee table. I don't know how to write that in DAX. I should note that I have a date table.

[v-yuezhe-msft · Accepted solution]

@powerbieber,

1.Create the following columns in leads table.

Week = WEEKNUM(leads[date])
year = YEAR(leads[date])

2. Create the following measures in leads table.

countofleads = COUNTA(leads[leadID])
checkleads = IF([countofleads]>=5,1,0)

3. Create a new table using DAX below.
newtable = SUMMARIZE(leads,leads[year],leads[Week],employee[employeeName],"leadscount",[countofleads],"checkid",[checkleads])

4. Create the following measure in the new table.
numberofweeks = CALCULATE(COUNT(newtable[Week]),FILTER(newtable,newtable[checkid]=1))

5. Create visuals as below.

Regards,
Lydia

[powerbieber]

Thank you very much for your help. It worked like a charm.

But now I want to add another layer to this. There is a third column in the emloyee table that lists the manager ID for every employee. The employee table is linked to the manager table which has the name and ID of every manager. 

I need to calculate the success rate of the managers based on how many of their employees were on target last week. For example, a manager who has 8 employees of which 6 were on target (Having 5 leads a week or more) would be 75% successful.

I wrote the DAX code for a calculated column on the manager table that shows how many employees each manager manages. However, I'm having trouble writing code for a column that shows how many employees were on target last week. 

Please help do this too Lydia.

Again I really appreciate your help.