Consider a use case where you need to do what-if analysis on multiple expenditure items across different categories. How does a ten per cent increase in salary of top management and a twenty per cent cut in the expenditure spent for advertising reflect on you bottom-line?? Find out using Tableau.
Should you create a parameter for each expense item and vary them to determine changes to you bottom-line? It would take you a long time to create them and name them properly and it any case your dashboard would be cluttered by them.
How about we create a common set of parameters that link themselves with whatever category of expense that is currently available on your crosstab. Let’s assume here that we have fifty expense elements across ten different categories.
1. First create a nominal number of parameters, for example you can choose the maximum number of elements in each category but that would lead to a few parameters cluttering up the dashboard landscape without any functionality in case of categories with expense elements less than the maximum, or you can use to compare the top N elements. Here we choose to use the top five elements in each category. The parameters will represent the factors by which you increase or decrease a given expenditure.
2. Choose the values in the parameter to be of float type and to range from 0.1 to 2 indicating a range of change from a 90% decrease to a 100% increase. You can also choose to have continuous values if more precision is required.
3. Now create a crosstab with the top five expenses for each category and the expense measure .Also create an option of filtering the crosstab to display the expense items of one category at a time using quick filters. Now all that remains is to link the parameters that we created to the expense elements based on the category being selected dynamically.
4. Now create a table calculation on the expense measure to multiply each row with one parameter .The row number is obtained using the index function.
5. This will give you a column for your crosstab that varies dynamically based on the category selected and the values selected in the parameter. Now create a copy of the original expense measure and add it to your crosstab visualization for reference with the changing expense measure. Also you can create another column that describes the savings in monetary terms.
6. Now insert that sheet into a dashboard and make the parameters floating and to adjust their placement in order to make it more neat and intuitive.
7. Now create another table calculation column that calculates the sum of savings across individual costs to find out the total savings.
8. Here I have used a combination of lookup and index functions to display the total savings in this category only once in, order to make the presentation better.
Since all the calculations are being done as table calculations, the values generated are truly dynamic,ie they are valid only for the current combination of parameter values and the calculated fields cannot be used in any other visualization.
Now that you know how to link parameters dynamically, have fun modifying it for any other use cases that you might have.