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

    Show Sales Persons over 100% by Region?

    Nathan Lee



      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






      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?