2 Replies Latest reply on Jun 6, 2018 9:38 AM by Nathan Lee

# Show Sales Persons over 100% by Region?

Hi,

I need some help summing measures that refer to a lower level dimension at a higher dimension. I want to count how many Sales Persons are over 100% Quota at the Region level.

Using the "QuotaAttainment" sheet from the "Commission Model" dashboard in the Superstore sample workbook, I am trying to count the number of Sales Persons over 100% attainment by Region. I have a calculated field called "Over 100% Quota Achievement":

IF [% of quota achieved] > 1.0 THEN

1

ELSE

0

END

When I have Sales Person showing as a Dimension, I do get a 1 for each line with [% of quota achieved] over 100%. I can see there are 2 Sales Persons with a '1' in the Central Region. What I need to be able to do is remove the Sales Person Dimension, so I can see only Region, and have it show me a '2', indicating that there are two Sales Persons in the Central Region over 100%.

I just can't figure out how to count up the results of a lower level (Sales Person) calculation at a higher level (Region).

I've attached a workbook showing what I've talked about. How would I change it so that I can remove Sales Person and get a total number of Sales Persons over 100% at the Region level?

• ###### 1. Re: Show Sales Persons over 100% by Region?

Try creating a calculated field based on your % of Quota Achielved that looks like:

Sales Rep Quota

{Include [Sales Person]: [% of Quota Achieved}

Then another calculated field mirroring how you're doing above.

IF [Sales Rep Quota field you just crated] > 1.0 THEN

1

ELSE

0

END

1 of 1 people found this helpful
• ###### 2. Re: Show Sales Persons over 100% by Region?

Outstanding. Thank you, Mr. Cronin.

"Include" did what I needed. I did just what you said, and it provided a correct answer. As a bonus, it even gives me an appropriate answer when I am displaying the Sales Person dimension.