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

    Use filtering while maintaining Hierarchy

    Ali Siddiqui



      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



      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





      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.