How to create rank button with parameter action
Rank is a very common index in business world. You may need see salesrep rank in different department, or product sales rank in different state. But some department or state has more salesrep or product than others. If you let all salesreps or products show, some chart will be too much. So you may need button like "1-10", "11-20" etc to show 10 each time. When user chooses "1-10" button, it will only show rank from 1 to 10, when chooses "21-30" button, it will only show rank from 21 to 30. After Tableau has parameter action, this becomes easy.
Let us use Sample - Superstore to achieve this purpose: we want to see city sales rank in each state.
We create a calculated field to get city numbers in each state which can indicate what is last button for this state.
State Names: if no state is selected, it will show "all countries", otherwise show the state name.
IF COUNTD([State])>1 THEN 'all countries'
When user choose different state, he should know some button is not useful. For example, if a state only has 13 cities wih sales, then button after "21-30" are not useful.
Button: comes from another excel sheet.
Then we create a parameter called "Button Parameter" to let user choose rank:
Button Start: decide first rank of city.
Button End: decide last rank of city.
CASE [Button Parameter]
WHEN "1-10" THEN 1
WHEN "11-20" THEN 11
WHEN "21-30" THEN 21
WHEN "31-40" THEN 31
WHEN "41-50" THEN 41
WHEN "51-60" THEN 51
CASE [Button Parameter]
WHEN "1-10" THEN 10
WHEN "11-20" THEN 20
WHEN "21-30" THEN 30
WHEN "31-40" THEN 40
WHEN "41-50" THEN 50
WHEN "51-60" THEN 60
Choosd Cityies: decide which cities show.
[Index]<=MIN([Button End]) AND [Index]>=MIN([Button Start])
To make it more flexible for more user, we create a parameter called "Column Number Parameter" to let end use choose how many button column, if you have 6 buttons to show and this parameter is 3, then you will have 3 columns and 2 rows of button as below image:
Another parameter called "State Parameter" to let user choose a state from map:
Then we need create 4 calculated fields in button excel sheet to decide buttons position.
Index: calculate index based on Button
Column: decide which column
IIF(([Index] % [Column Number Parameter])=0,[Column Number Parameter],[Index] % [Column Number Parameter])
Row Values: calculate Rows
[Index]/[Column Number Parameter]
Row: decide which row
IF [Row Values]=INT([Row Values]) THEN
Now we can start build our dashboard,
First hold control key and select State and Sales together and use Show me to create a filled map as below, let's call this sheet "Map"
Use same way to create a "City Sales" sheet by using City and Sales and make it sort. Drag Choosed Cities to filter and keep True.
Create a "Title" Sheet to show some info based on what user choosed by dragging State Name, City Numbers, Button Start and Button End to text:
Now the tricky part is creating button sheet.
1. Go to Button excel sheet, drag Column to Cloumns, Row to Rows and Button to Detail.
2. Add blank to both Columns and Rows, choose Marks as Square to make it like a button shape.
3. For Row and Column table calculation, choose using button.
4. Then format row and column divider, choose white color and make it biggest then it looks like real button.
Now, create a dashbord and put these sheets in it, and make Map as a filter for other sheets.
Then we set first parameter action which is choose state:
Second parmameter action which is choose rank:
Then you can get final:
When you select a state on map, the title will change accordingly to tell how how many cities in this state have sales so that let user choose right rank buttons.