cancel
Showing results for
Did you mean:
Frequent Visitor

## Totals not adding up correctly

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]
)```
1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team

## Re: Totals not adding up correctly

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 REPLIES 5
Community Support Team

## Re: Totals not adding up correctly

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.

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

## Re: Totals not adding up correctly

Is this problem sloved?
If it is sloved, could you kindly accept it as a solution to close this case?
If not, please let me know.

Best Regards
Maggie
Frequent Visitor

## Re: Totals not adding up correctly

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?

Community Support Team

## Re: Totals not adding up correctly

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

## Re: Totals not adding up correctly

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.

Announcements

Kudos to you if you earned one of these! Check your inbox for a notification.

#### Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

#### Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)
Users online (1,614)