4 Replies Latest reply on Feb 16, 2016 12:10 PM by Adam Crahen

    Use filtering while maintaining Hierarchy

    Ali Siddiqui

      Hello,

       

      I have a hierarchy/dimensions of Company Name, Location, Office Number, Sales Agent. I have one measure of Sales Dollars. I am trying to obtain a list of agents that meet a certain criteria, as defined by filters/parameters on measures.

       

      I would like to obtain all sales agents who made less than $50,000 in a given period. So I set up a basic filter and that works fine.

       

      Now, I want to use my hierarchy, and aggregate up at the Office Number or any other level. But when I hit the minus button ( - ) along the top, Tableau sums up the Sales Dollars, and I lose my list altogether.

       

       

      Example, If office A has 2 agents, who made $49,000 in sales, I would see the following:

       

      Office NumberSales AgentSales DollarNumber of Records
      ABCJon$49,0001
      ABCMary$49,0001

       

       

      I would like to be able to aggregate this, to see Office A, 2 agents, Sum(Sales) = $98,000.

       

      Desired output:

       

      Office NumberSales AgentSales DollarsNumber of Records
      ABC$98,0002

       

       

       

       

      However, the filter of $50,000 excludes both agents, since it's aggregating now at the Office level.

       

      Current output:

       

      Office NumberSales AgentSales DollarsNumber of Records
      - - --

       

       

       

      Is there any way for me to maintain the filter at the agent level, but still be able to aggregate?

       

      I have attached a sample report for use. Based on this data, if I collapse the hierarchy in the Rows, I lose the list of agents.