cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
renevandeveen Frequent Visitor
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
Community Support Team

Re: Totals not adding up correctly

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.

View solution in original post

5 REPLIES 5
Community Support Team
Community Support Team

Re: Totals not adding up correctly

Hi @renevandeveen 

I  create a sample as below

Capture27.JPG

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
Community Support Team

Re: Totals not adding up correctly

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
renevandeveen Frequent Visitor
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
Community Support Team

Re: Totals not adding up correctly

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.

View solution in original post

renevandeveen Frequent Visitor
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. 

 

Your help is really appreciated!

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

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

Microsoft Implementation for Communities Wins Award

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

Power Platform World Tour

Find out where you can attend!

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