1 Reply Latest reply on Jun 15, 2014 7:56 PM by Noah Salvaterra

    Comparing measures to total when hierarchy is filtered

    John Miller

      Hi all,


      I'm working with a manager hierarchy where there are 5 levels of managers (e.g. Tom is the top-line mgr with 5 direct reports, each direct report has 4 direct reports, and so on).  I'm displaying survey results for each top-down manager in the hierarchy with a parameter to view by top down level. 


      Tom SmithSally SmithRona Collings 
      Tom SmithSally SmithAnita Deemer 
      Tom SmithSally SmithRobin Mcconkey 
      Tom SmithSally SmithCharis Barnes 
      Tom SmithKelly ThompsonStuart Hellickson 
      Tom SmithKelly ThompsonKatrice Proto 
      Tom SmithKelly ThompsonEvonne Lacroix 
      Tom SmithKelly ThompsonTheodore Parrott 
      Tom SmithMike JohnsonKattie Fuller 
      Tom SmithMike JohnsonVesta Durfee 
      Tom SmithMike JohnsonBelinda Providence 
      Tom SmithMike JohnsonMeta Foos 
      Tom SmithVictor HaynesSharron Bolz 
      Tom SmithVictor HaynesMissy Teter 
      Tom SmithVictor HaynesAshli Hund 
      Tom SmithVictor HaynesBlair Geeter 
      Tom SmithJanice CrawfordRosalva Alonzo 
      Tom SmithJanice CrawfordMarcy Tarango 
      Tom SmithJanice CrawfordCorrie Duarte 
      Tom SmithJanice CrawfordSonja Wingate


      So for example, if I choose Level 3 from the parameter, i see a list of all managers 2 levels down from Tom (all 20 mgrs) with the survey results aggregated for those employees who roll up to them.  Also, i have a quick filter on the dashboard so that you can choose a level of manager for whom you'd like to see results by.  Again, as an example, if i choose Level 3 manager from the parameter, but i want to see the results for a particular Level 2 manager such as Sally Smith, i can set the Manager Level 2 quick filter to Sally Smith and see the results for each level 3 manager beneath her.


      So here's the rub, i have my results displayed in a horizontal bar chart for % of favorable responses, and another horizontal bar chart for difference from the overall average for the entire survey.  This difference chart works fine when i look at the top level.  But as i filter down from there, the difference from total is off because the total has now been filtered. 

      What i'm trying to figure out is whether or not there is another way to accomplish what i'm trying to do?  Thoughts?


      Part two of my question is whether or not i can create a visual of this hierarchy where if i'm looking at the results for each manager beneath Tom Smith (bar chart with one line per manager for Sally Smith,Kelly Thompson, etc.) can i click on the bar for Sally Smith and now have the visualization filter Sally SMith and display a bar char for each manager beneath her (Rona Collings, Anita Deemer, etc.)?'


      All help is appreciated. Thanks,