I think you got pretty close with your rank fields, etc.
Here are the changes I made to your workbook. You can see a little more detail here. Creating "Other" Bin of smallest measures
1. Added two parameters: Show top N airlines (integer) and Show all (boolean)
Even if you don't add this flexibility to your end user view, its helpful to validate everything is working and perhaps choose a better n-value.
2. Added calculated field In top N (IF INDEX() <= Show top N airlines).
You could probably incorporate this logic into the other calculations, but I think it's simpler to use a calculated field.
3. Changed Rank (group) to airline_name (top n), since the field is really the airline name.
I also edited this table calc to support Show all and to add the count of others. See comments in table calc.
4. Added calculated field for current pax (display) and prev pax (display)
This aggregates the other category --- and maybe was the key piece you were missing.
5. Added calculated fields for current pax market share and % change year over year
These both need to be based on the (display) version of the fields.
Building the view
1. Right click on parameters and select "show parameter control"
2. Put airline_name on the level of detail shelf.
2b. Right-click on airline_name pill on detail shelf, select Sort > Descending , current pax SUM
3. Put current pax (display) on the columns shelf.
4. Put rank (display) on the rows shelf and change to discrete.
5. Put airline_name (top n) on the rows shelf.
6. Select Compute using "airline_name" for table calc pills added above.
7. Add current pax (market share), % change vs prev year (display) --- again, make sure to right-click and verify compute using "airline name"
8. Format > pane lines > Row Divider > move slider to left to eliminate lines between rows.
dynamic_group_sample_jimw.twbx.zip 447.1 KB
Jim, you are a wizard! I'm still having trouble grasping table calcs so I doubt I would have gotten there! Is there any way to extend the market share and % diff calcs to the "other" group? Would it be easier to create a separate view and just pull it adjacent to the bottom in a dashboard?
Thanks for the kind words. It wasn't that long ago I was beginning to scratch the surface of table calcs, and I'm sure you'll be a wiz before you know it.
To show the market share bar and yearly change bars / data, remove the IF [In Top N] from these calcs, and, for the market share, replace SUM([current pax ]) with the already aggregated [current pax (display)]. (This will prevent overprinting.)
[current pax market share] = [current pax (display)] / TOTAL(SUM([current pax]))
[% change vs prev yr (display)] = ([current pax (display)] / [prev pax (display)]) - 1
One thing that's a bit confusing is that the Top N will be based on the current year. If you then change the year, the Top N airlines may change and, the sum of "other" may change.
For example, in 2011 Frontier was ranked 10 and the "211 Other" airlines summed to 3,169,449 for 2011 and 3,246,842 for 2010. But if you choose year 2010, then Jet Blue replaces Frontier and the "211 Other" airlines sum to 3,212,330 for the current 2010 year. In other words, the "Other" sum for the previous year is dependent on the current year.
Makes sense & looks great, thank you for your help!
I have the same issue, I followed the sample it works.Can I ask a quick one, is it possible to make a pie chart of the same i.e. dynamically.