5 Replies Latest reply on Mar 3, 2016 10:25 AM by David Carr

# Hierarchy Calculation

Hi,

As you will see in the attached workbook, I have a simple table with quota attainment for sales reps.

The data at the monthly level is exactly what I need, the 'Quota Attainment' calculation works correctly, and the 'hit or miss' coloring works as well.

What I cannot solve however, is when I collapse for a quarterly view, the calculation and coloring get thrown off.

For the calculation, I want their quarterly attainment, not the sum of their monthly attainments or average of their monthly attainments. For instance, Hannah's quarterly quota attainment should be 93% (84/90). I would also like the colors to stay in place at the quarterly level.

Let me know if anyone is able to figure this out!

Thanks

David

• ###### 1. Re: Hierarchy Calculation

David,

Couple of change needed.

(or average?)

Quarter View

Month view

You can change color as you like.

Thanks,

Shin

8.3 attached

2 of 2 people found this helpful
• ###### 2. Re: Hierarchy Calculation

Hi Shin!

Thanks for this. Definitely solved the color problem.

However, I am still trying to solve the calculation issue at a quarterly view.

As you'll see in the attached, Hannah's average Q1 quota attainment is 158%. It's showing this because 158 is the average of Jan, Feb, March.

However this is incorrect at a quarterly level as it should be 120% (84/70).

Would you know how to solve this issue?

Thanks!

Dave

• ###### 3. Re: Hierarchy Calculation

Hi David,

Change the quota attainment formula and wrap it under a SUM(). What you have is a row level calc and what you have on the view is aggregated detail which is why you are getting wrong results. Additionally right click on quota attainment field and change default properties to show show 2 decimals:

1 of 1 people found this helpful
• ###### 4. Re: Hierarchy Calculation

Thank you for the support, Pooja.

Shin

1 of 1 people found this helpful
• ###### 5. Re: Hierarchy Calculation

Great, it works, thank you Shin and Pooja!