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

    Show Sales Persons over 100% by Region?

    Nathan Lee
    Nathan Lee Jun 6, 2018 8:54 AM

      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?