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?

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

• ###### 1. Re: Counting Rows in a Filtered View When the Filter Contains a Table Calculation - Is There an Elegant Solution Yet?

HI Mark,

I don't have idea to accomplish this with Filter.

Reasonable alternative is (even if  probably you don't like it though) is two parameters.

Thanks,

Shin

• ###### 2. Re: Counting Rows in a Filtered View When the Filter Contains a Table Calculation - Is There an Elegant Solution Yet?

Shinichiro - if you already read the message below I left a couple hours ago ignore it because it now works! I just had to rebuild the summary view more closely from the original view(s) with the vendor rows. I'll write a more complete response tomorrow but THANKS this is a very acceptable solution for my particular use case.

Mark Usher

*********************************************************************************************************************************************************************************************

Thanks for this Shinichiro. Conceptually your approach using the parameters looks like it would meet my requirements. Unfortunately although I see it working perfectly fine with the superstore dataset unfortunately I've yet to make it work with my "real" data. The Group Count measure works fine as a repeated row in a table with all the vendors but the step to the summary sheet does not work. The group count values for each group change and the sort order is the wrong way round. I suspect the superstore example is an oversimplified dataset that somehow allowed the parameter example to work whereas a real life example creates an issue. Thanks again for your effort though.