Totals not adding up correctly

[renevandeveen]

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]
    )

[renevandeveen]

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?

[v-juanli-msft · Accepted solution]

Hi @renevandeveen 

I update my previous post, please download the pbix file to see how the measures 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.

[renevandeveen]

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!

[v-juanli-msft]

Hi @renevandeveen 

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

[v-juanli-msft]

Hi @renevandeveen 

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.