Forum Discussion
Looping through a table and calculating values, then summing up those values
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,
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
4 Replies
powerbieber,
Could you please share dummy data of your table and post the expected result based on the dummy data here?
Regards,
LydiaThere 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.
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
