Hi Mark,

Here you go.

Given that the goal is to expand or collapse a set of rows based on a measure (% of Total) that is already a table calculation, it gets a mite sticky. You'd created the Procedure Type (2%) field that assigned all the ones with less than 2% of the total to the Other group, but now we need to generate a total of the % of Totals of all Procedure Types in the Other group. Ideally we'd be able to just have another table calculation that sums up the % of Total and partitions on the Procedure Type (2%) field, but Tableau does not support partitioning by table calcs. In addition, Tableau can't sort a discrete table calculation result other than manually, and we need a dynamic sort.

Over the last few weeks I've been running into multiple situations that would have been made possible of tremendously easier if Tableau partitioned by table calcs, and this problem seemed like one that was simple enough that I could find a workaround. So, here's what I did, with some trial and error:

- Created the Day of Procedure Date calc that is is DATETRUNC('day',[ProcedureDate]). This simplifies how we look at the dates in table calcs.

- Created the % to Hide Parameter to make the number of procedures that are grouped into the Other bucket configurable.

- Adjusted the ProcedureType (2%) calc to respect the Expanded (show all values of Procedure Type) or Collapsed view, and set it to use the True % of Total calc below, I called the new version ProcedureType (2%) (revised)

- Put ProcedureType on the Level of Detail, with sort set to Descending/Number of Records/Sum.

- Created the True % of Total field that is SUM([Number of Records])/TOTAL(SUM([Number of Records])). That's pretty straightforward.

- Created the Index to 1st Other calc:

IF [Procedure Type (2%) (revised)] = "Other" AND LOOKUP([Procedure Type (2%) (revised)],-1) != "Other" THEN

//return the index of the first row in Other

INDEX()

ELSEIF [Procedure Type (2%) (revised)] = "Other" THEN //get the value of INDEX() found above

PREVIOUS_VALUE(0)

ELSE

//any row that is not collapsed

0

END

This identifies which row in the results goes with the start of the Other group that is created by the ProcedureType (2%) calc.

- Created the Last calc:

LAST()

This is due to some issue I don't quite understand with how Tableau was sorting the data when I included the dates in the view, having this available as an independent field fixed the problem.

- Created the % of Total calc:

IF [Expanded/Collapsed] = "Expanded" THEN

[True % of Total]

ELSE //view is collapsed

IF [Index to 1st Other] = 0 THEN //we're in one of the non-collapsed rows

[True % of Total]

ELSEIF [Index to 1st Other] = INDEX() THEN // this is the first row of the Other group

WINDOW_SUM(SUM([Number of Records]),-(SIZE()-[Index to 1st Other])+[Last], [Last])/TOTAL(SUM([Number of Records]))

END //we're not performing the calculation for any other rows in the Other group,

// or else we'll get overlapping rows

END

This calculation returns the True % of Total for all rows in the Expanded view, when Collapsed it returns the True % of Total for rows that aren't in the Other group, and then for Other returns the % of total for all rows in Other.

The Compute using for % of Total and its nested calcs is set to Advanced.. with Compute using ProductType and Sort on Number of Records/Sum/Descending. Now, this works great, except that the view won't properly sort without a little help. When it comes to sorting table calculations that are discrete (blue) pills, the only options are a manual sort or the data source order, the latter is numeric or alphabetical ascending.

- Therefore, we can create a calculation that returns a numeric number that Tableau will sort by, put that as the left-most discrete (blue) pill on the Rows shelf, and then uncheck Show Header for that pill so it is hidden and provides an invisible sort. Here's the Sort calculation:

IF [Expanded/Collapsed] = "Expanded" Or [Index to 1st Other] = 0 THEN

-SUM([Number of Records])

ELSE

0

END

That works for the basic view shown in 1. working with no dates.

When dates come into the view, the sorting changes so we have to set up the table calculations correctly. Every single table calculation (including all the nesting for the % of Total calc) needs to be set to: Compute Using: Advanced, with Day of Procedure Date above ProcedureType, sorted on Number of Records/Sum/Descending, restart every Day of Procedure Date. This way the procedures for each day will be sorted for that day. Anything else causes weird things to happen, like the Other total being incorrect.

A working view of the dates can be seen in 2. working with dates.

Now we can create the bar chart showing % of total by day, that's in 3. working bar chart.

Here's a simplified review of the process, for my sake as much as anyone else's:

1. Create a table calc measure, like % of Total that you want to view across a generated set of partition(s).

2. Create a table calc that generates the partition(s), that's the Procedure Type (2%) (revised) calc here.

3. Flag the starting row in partition of interest, that's the Index to 1st Other calc.

4. Create the displayed version of the measure that shows the measure from step 1 (% of Total) for the rows not in the partition of interest, and a custom version of the measure that only operates within the partition of interest, that's the Index to 1st Other calc.

5. If necessary, set up a measure that will be used as a hidden header to sort the results.

Let me know if this works for you!

Jonathan