“This visualization is great! But I don’t like the scroll bar as I’m mostly interested in seeing the top numbers. This can be solved with a top-n view. But the thing is, I also don’t want to limit the view as I know some people might be interested in seeing more data than me. Are there any alternatives to scroll bars while keeping the possibility to see complete numbers?”

This is one of the questions I receive regularly. It is interesting to reckon that many people prefer not to have a scroll bar in their dashboard. Especially when they only need to see some data that makes up the biggest contribution. So what are the alternatives of using a scroll bar while still keeping the complete data?

Roll up small numbers using others

Roll up small numbers using others


Use-case:
This is a good alternative to roll up the smaller or less significant numbers as Others. It allows the user to see the Subcategories that make up the biggest percentage of Sales, while still being able to see the rest in Others.

Data:
Here we are going to use the Sample Superstore data

Parameters:

  • Contribution %: Set Data type as Float → Set Allowable values as Range → Minimum 0.01 → Maximum as 0.15 → Display Value as Percentage with Decimal Places as 2 → Set Current Value as 0.08

Calculated Fields:

  • Percent of Total: SUM([Sales])/MAX({SUM([Sales])})
  • %Contribution: SUM([Sales])/MAX({SUM([Sales])})
  • Create Sub-Category Set:
    In the General tab, choose Use All.
    In the Condition tab, select by Formula and enter [Percent of Total]>=[Contribution %]
  • Sub-Category & Others: IF [Sub-Category Set] THEN [Sub-Category] ELSE 'Others' END
  • # of Sub-Category | In: {SUM({INCLUDE [Sub-Category]: COUNTD((IF [Sub-Category Set] THEN [Sub-Category] END))})}
  • # of Sub-Category | Out: {SUM({INCLUDE [Sub-Category]: COUNTD((IF NOT [Sub-Category Set] THEN [Sub-Category] END))})}
  • Sub-Category Label | In: IF MAX([# of Sub-Category | In])>1 THEN 'states' else 'state' END
  • Sub-Category Color | Individual: IF [Sub-Category Set] THEN [Sub-Category & Others] END
  • Sub-Category Color | Others: IF NOT [Sub-Category Set] THEN [Sub-Category & Others] END

Worksheet:

  • Change the Mark Type to Bar
  • Drag Sales into Column
  • Right click on Sales and Add Table Calculations. Select Percent of Total and compute using Table(Down).
  • Drag Sub-Category & Others into Rows
  • Right click on Sub-Category & Others and select Sort. Choose Sort By Field and Ascending order. In Field Name, search for Sort. And leave Aggregation as by Default Sum
  • Drag Sub-Category Set as Color to the Marks Card
  • Drag Sales to marks card as Text. Add Table Calculation, select Percentage of Total and calculate using Table(Down)
  • Drag Sales to marks card as Text
  • Click on Label and select icon for Left align. Place this into the text box.
    <Sub-Category & Others>
    <% of Total SUM(Sales)> | <SUM(Sales)>
    Drag # of Sub-Category | Out, # of Sub-Category | In, Sub-Category Label | Out, Sub-Category Label | In, Sub-Category Color | Individual, Sub-Category Color | Others to Marks Card as Detail



Clickable Detail Page to see the full list


Clickable detail page to see the full list

Use-case:
What if in addition to Alternative 1 above, the user still wants to see the full list of Sub-Categories?

Worksheet:

  • Create a duplicate of the worksheet from Alternative 1 above and call it “Bar Chart Full”. Replace the Rows and Marks Card text from Sub-Category & Others to Sub-Category.
  • Take out all of the marks except for all the Text one.
  • Create a blank worksheet and name it “Category”.
  • Create a parameter with Name Category Parameter, Data Type String, Allowable Values as List, and select toggle Fixed → add values from Category
  • Create a calculated field called Filter | Category: [Category Parameter] = [Category]
  • Place this calculation into the Bar Chart Full filter card and thick only the True box.

Dashboard 1 (Main):

  • Duplicate this dashboard
  • Add a floating Navigation button to the first “original” dashboard. In the Edit Button section, choose Navigate to the second duplicate dashboard. Select Button Style as Text. And Title as “See Full List”

Dashboard 2 (Duplicate Dashboard):

  • Add a floating Vertical/Blank container on top of this dashboard
  • Set the background color to grey with Opacity 60%
  • Add a second floating Vertical container with a smaller size than the first one. Set the background color to white. In this example, I added 2 times vertical containers to create a grey border around the pop-up page. But this is optional.
  • Drag the “Bar Chart Full” and “Category” worksheets into this floating container.
  • Add navigation button type Icon, and select Navigate To the Dashboard 1 (Main)
  • Create a Dashboard Parameter Action and call this Category Parameter. Thick the Category worksheet and Run Action on Select
  • Select Parameter as Category Parameter, select field Category, and in Clearing the section will: select Keep current value.



Create pagination with Next/Previous Page button


Create pagination with next/previous page button


Use-Case
:
“Yes, but I don’t want to see Others in my bar chart in the first place”. In this case, we can create Next/Previous page buttons that will let us divide the list into several pages. This is a great alternative in case you have a huge amount of data that you want to show.

Parameters:

  • Row Number: Set Data type asInteger → Display Value asAutomatic → Current Value as10 → Set Allowable values asAll

Calculated Fields:

  • Sales | Rank Unique: RANK_UNIQUE(SUM([Sales]))
  • Page Number: ((([Sales | Rank Unique]-1) - (([Sales | Rank Unique] - 1) % [Row Number]))/[Row Number]) + 1
  • Select Page: IFNULL(STR(([Page Number] - 1) * [Row Number] + 1) + "-" + STR(([Page Number] * [Row Number])), "NULLs")

Worksheet:

  • Drag Sales to Columns. Click Add Table Calculation and select Percent of Total, compute using Table(Down)
  • Drag Sub-Category to Rows and sort by Sales in Ascending order
  • Drag Sales | Rank Unique to marks card as Detail
  • Drag Sub-Category Sales to marks card as Text
  • Drag Sales to the marks card as Text. Click Add Table Calculation and select Percent of Total, compute using Table(Down)
  • Drag Sales to the marks card as Text
  • Click on Label and select icon for Left align. Place this into the text box.
    <Sub-Category & Others>
    <% of Total SUM(Sales)> | <SUM(Sales)>
  • Drag Select Page to the filter pane, click on the Edit Calculation and choose compute using Sub-Category (unselect the Sub-Category Sales)
  • Change the filter type to Single Value (Slider)

Extra:

This method can also be applied to a long table by leveraging the sheet swapping.

  • Create an order table sheet and name it “Order Table
  • Create a parameter called View with Integer Data Type and Allowable Value as List
  • In the List, add 1 and display it as “Sub-Category Sales”. Add 2 and display it as “Order Table
  • Create a calculated field [View] and called it Filter: View
  • Change the Filter: View type from Measure to Dimension
  • In the Order Table sheet, add Filter: View and select Custom Value List, write 2 and click +
  • In the Sub-Category bar chart sheet, add Filter: View and select Custom Value List, write 1 and click +
  • Add a vertical container to the dashboard
  • Place the Order Table sheet and the Sub-Category bar chart sheet into the container



Create pagination with a Page Number button


Create pagination with a page number button


This is a similar concept as alternative 3 above, but instead of using a default slider feature, we are going to create a button that allows users to select the page number.

Parameters:

  • Page: Set Data type as String → Current Value as 1 → Allowable values as All

Calculated Fields:

  • Dummy Page Number: IF [Region] = 'Central' THEN '1'
    ELSEIF [Region] = 'East' THEN '2'
    ELSEIF [Region] = 'South' THEN '3'
    ELSEIF [Region] = 'West' THEN '4'
    END
  • Color | Page Number: [Page] = [Dummy Page Number]
  • Row Page: CASE [Page]
    WHEN '1' THEN INDEX() <= 5
    WHEN '2' THEN INDEX() > 5 AND INDEX() <= 10
    WHEN '3' THEN INDEX() > 10 AND INDEX() <= 15
    WHEN '4' THEN INDEX() > 15
    END

Worksheet:

  • Create a  new worksheet called Category Page
  • Drag Category Page to Columns
  • Drag Category Page to marks card as Text
  • Drag Color | Page Number to marks card as Color
  • Create a Sub-Category sales worksheet (Sales in Columns, Sub-Category in Rows)
  • Drag Row Page to the filter pane and set as True

Dashboard:

 

  • Create a Dashboard Parameter Actions by going to Dashboard → Actions → Add Action → Change Parameter
  • Add Name: Page Button
  • Run Action on Select
  • In Source Sheet, select only the Category Page sheet
  • Set Target Parameter as Page
  • Set Field as Dummy Page Number
  • Set Aggregation to None
  • Set clearing section will Keep current value
  • Click Ok, and now you should be able to click on the page button and see the relevant number of bars



Dynamically drilling-down using Parameter Actions


Dynamically Drilling-Down using Parameter Actions

 

Use-Case:
Last but not least, in case a user doesn’t need to show the Sub-Categories all at once, we can also leverage a parameter action to drill down from a selected Category.

Parameters:

  • Parameter | Category or Sub-Category : Set Data type as String → Values when Workbook Opens as “Furniture” → Set Allowable values as All

Calculated Fields:

  • Category Drilldown:IF [Parameter | Category or Sub-Category] = [Category] THEN [Sub-Category] ELSE [Category] END

Worksheet:

  • Create a Sub-Category sales worksheet
  • Drag Sales into Column
  • Drag Category into Columns
  • Drag Category Drilldown into Rows

Dashboard:

  • Create a Dashboard Parameter Actions by going to Dashboard → Actions → Add Action → Change Parameter
  • Add Name: Drill-Down Parameter
  • Run Action on Select
  • In Source Sheets, select only the Sub-Category sales sheet
  • Set Target Parameter as Parameter | Category or Sub-Category
  • Set Field as Category Drilldown
  • Set Aggregation to None
  • Set clearing section will Keep current value
  • Click Ok, and now you should be able to click one Category and see the bars that represent Sales of each Sub-Category

I published this workbook to Tableau Public. Go check it out if you’re interested in implementing any of these solutions. Note that there are many things you can do to avoid the endless scrollbar and this is a handy guide to help you start.

But if you are interested to know more about Tableau and what the possibilities are for making your vizzes even better, don’t forget to check out our upcoming training courses by clicking on the Events and we will be more than happy to help. Happy discovery!


- Issye

Psssst... never miss out!

Stay informed of our recent developments, newest projects and upcoming events.