2 Replies Latest reply on Oct 23, 2018 8:47 PM by Mark Usher

    Counting Rows in a Filtered View When the Filter Contains a Table Calculation - Is There an Elegant Solution Yet?

    Mark Usher

      I know this question has been asked by a few different folks in slightly different ways but - anyway - here's the issue I'm struggling with. I'll very often want to segment my data into groups based on a Running Sum Percent of Total that is calculated after I have sorted my data from high to low. Using the Superstore dataset as an example (for which I have attached a workbook) I will then very often want to ask questions like:

       

      - How many products make up the top 80% of total sales? Call this Group A.

      - How many products make up the next 10% of sales? Call this Group B.

      - How many products make up the bottom 10% of sales? Call this Group C.

       

      In the attached workbook I have created 3 worksheets where each worksheet is sales by product sorted high sales to low. The only difference between the 3 worksheets is that the filter for Running Sum % of Total (a table calculation, obviously) is different in each. The first sheet is for Group A with the filter set to 0-80%. the second sheet Group B with the filter set 80-90% and the third sheet for Group C with the filter set to 90-100%.

       

      When I use SIZE( ) to give the number of rows for each sheet (which is the answer I want) I get the same number for each sheet 1856, the total number of rows ignoring the filter.

       

      I know WHY the above is happening - filters consisting of a table function only affect the view, not the calculations - what I want to know is there a way around this that allows me to get to the answer I need so that as the underlying data is updated I am able to have calculations that always give me correct numbers of items in each of the filtered views when a table calculation is involved? I've seen some workarounds using  counters and some pretty involved SQL code which are all commendable but don't seem to "quite work". Is this an area where there could be some progress towards an elegant solution or will this likely be something Tableau was never really designed to tackle? Which is fine.... it would just be good to know so we can think of other approaches.

       

      Thanks,

      Mark Usher