Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I would like to ask your help with the following problem. I am trying to calculate a YTD visit target for sales reps. Each sales rep has a visit target per week, and based on the selection of year and week in the slicers, the YTD total visit target should be calculated.
We distinguish three types of sales reps:
1. Employee started before the selected year and is still employed
2. Employee started within this year
3. Employee was employed at the beginning of this year but then quit
The formula has three different parts that account for the above three situations. The calculations lead to the right results on sales rep (employee) level. Yet, when calculating a total, the total doesn't add up.
This issue is caused by part 2 and 3 of the formula. This because the calculation isn't done on the sales rep (employee) level but on the aggregate level.
I am now looking for an option to calculate the visit target on the sales rep (employee) level and then add it all together.
One of the things I've tried was to use "hasonefilter". Yet, this only works to calculate the values per sales rep (employee). It then seems to be impossible to sum these values.
Could you please guide me in the right direction how to solve this issue?
YTD Visit Target = //part 1: employee is staying with the company CALCULATE ( SUM ( Employees[Target per Week] ), FILTER ( Employees, AND ( Employees[Starting Year] < [Selected Year], Employees[Isactive] = "TRUE" ) ) ) * [Selected Week] //part 2: employee is new to the company + ( CALCULATE ( SUM ( Employees[Target per Week] ), FILTER ( Employees, AND ( Employees[Starting Year] = [Selected Year], Employees[Starting Week] <= [Selected Week] ) ) ) * ( [Selected Week] - CALCULATE ( SUM ( Employees[Starting Week] ), FILTER ( Employees, AND ( Employees[Starting Year] = [Selected Year], Employees[Starting Week] <= [Selected Week] ) ) ) ) ) //part 3 employee has left the company + IF ( CALCULATE ( SUM ( Employees[Ending Week] ), FILTER ( Employees, Employees[Ending Year] = [Selected Year] ) ) < [Selected Week], ( CALCULATE ( SUM ( Employees[Target per Week] ), FILTER ( Employees, Employees[Ending Year] = [Selected Year] ) ) * CALCULATE ( SUM ( Employees[Ending Week] ), FILTER ( Employees, Employees[Ending Year] = [Selected Year] ) ) ), CALCULATE ( SUM ( Employees[Target per Week] ), FILTER ( Employees, Employees[Ending Year] = [Selected Year] ) ) * [Selected Week] )
Solved! Go to Solution.
I update my previous post, please download the pbix file to see how the measures created.
Hi Maggie,
Thank you for your help. Unfortunately though, the totals in your file do not add up as well. You can see this in measure 22, which adds up to 80 whereas it should be adding up to 95.
Any ideas how to solve this?
I update my previous post, please download the pbix file to see how the measures created.
Thanks Maggie! That did the trick. I was unaware that you could apply a SUMX to an already created measure. This due to the fact that I had all my measures in a seperate table without columns. Adding a column to this table did the trick and made the SUMX work.
Your help is really appreciated!
I create a sample as below
Explaination:
1. Employee started before the selected year and is still employed -> part 1 measures
2. Employee started within this year -> part 2 measures
3. Employee was employed at the beginning of this year but then quit -> part 3 measures
2. Employee started within this year
Based on my understanding, this part should contain the Employee started within this year including who ends before(or equal or after) selected year/week.
Also, when mutiplying " SUM ( Employees[Target per Week] ) " and "[selected week]-[starting week]",
if [selected week]=[starting week], the "[selected week]-[starting week]" will be 0, so i add 1 for the formula.
For more details , please dowload my pbix and see the measures i created.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |