How to change the visualisation of a Dashboard with Parameters

I recently helped someone out with a questions about how, in a published workbook, they would show a default table without any totals and then when the end user wanted to they could switch on the totals.

On its own this idea was unique but the way I solved it can be used to do all sorts of great things and you can use this in many different ways. I will show you two ways here but the great thing about Tableau is that there is so many ways to achieve the result you want.

Method 1

Step 1 – Create the Parameter

You can either make the parameter in the calculated field window or make it before you start. In this example I’ll make it before because I think it’s easier to follow.

We need to create four options on a list so I make a String type parameter and add in a list of values I want to use

– No Totals, Grand Totals, Bubble Chart and Map

Now that we have the parameter and we know what we want to show we can start to build the sheets that will make the dashboard.

Step 2 – Create the calculated fields

You need to create a calculated field that will show the measure but only when the Parameter matches. You do this by using the CASE statement as follows.

You do the same process for all the other sheets as well so if you copy this and then create four more calculated fields changing the text between the speech marks to match the other options from the parameter.

NOTE – The text must match exactly in case, spaces and spelling.

Step 3 – Create the sheets with the new fields

Now that we have the calculated fields and the parameter control all we need to do is create the sheets. To start with create the view you want to see using the normal field, it’s always helpful to know what you’re aiming for.

TIP – I like to show the Parameter control on each sheet so that I can test it as I build.

Next take the calculated field that relates to the “No Totals” and drop it over the SUM([Sales]) to replace it. There should be no change to your view. Next take it and drop it onto the Filter shelf and select At Least = 0

This is how the magic works later by setting this filter it will make the sheet blank (0 marks) when the Parameter is not set to “No Totals” just like below.

Rename the sheet and then you can move on to the next sheet following the same process.

TIP – It’s sometimes easier to duplicate the sheet and just drag and drop over the calculated fields to remake the original view.

Step 4 – Creating the Dashboard

Now that you have all the sheets that you want and they “disappear” when you change the Parameter control you need to add them onto your Dashboard to complete the affect.

If you create a dashboard and just drag and drop the sheets onto the canvas you will end up with the sheets all taking different amounts of space.

So the way I like to fix this is by just dragging out one to get the filter spacing and then I drag out the Vertical object box to the top of the sheet space. Then you just need to drag and drop all the sheets into the Vertical object to help with the spacing of the windows (You can use Horizontal in the same way).

Then if you select each sheet and make them the “Entire View” you will see that they will take up all the extra space.

The Finishing touches

The last part is now making it look nice and checking it does what you expect it to.

Hide all the Titles of the sheets,

As you can see from the above you can tell where the sheets are hiding and the titles also take up more space.

Remove any unwanted filters you have,

I like to make another sheet to replace the legends and drop the sheet onto the filter area.

I like to move the Parameter to the top

Make it a feature so the end user knows that they can use this to change the views.

Show off your skills to your colleagues!

var divElement = document.getElementById(‘viz1563536607759’); var vizElement = divElement.getElementsByTagName(‘object’)[0]; vizElement.style.width=’800px’;vizElement.style.height=’627px’; var scriptElement = document.createElement(‘script’); scriptElement.src = ‘https://public.tableau.com/javascripts/api/viz_v1.js’; vizElement.parentNode.insertBefore(scriptElement, vizElement);

Method 2

This method may appear simpler and it is in some ways but I think it is great when you mix it up with the skills from method 1.

Step 1 – Create a Parameter Dimension

From the first method we have a Parameter that has all the different views of the data we want to see. We can use this Parameter to create a calculated dimension that we can use on the filter shelf.

Create a new calculated field and simply add the Parameter into the formula area and give the field a good name.

Step 2 – Create a new sheets

You will need to create as many sheets as you have in your parameter like we did in method 1 but this time rather than adding the calculated measure fields to the rows and filters you can use the standard field of Sales and use the new Parameter filter.

Repeat this process to get all the sheets you need.

Step 3 – So why show you Method 1?

Method 1 shows you how a CASE statement works in relation to a Parameter control. The extra power this gives you is amazing and when you combine these methods you will be amazed at the results you can get. As an example I am going to now supercharge these sheets with end user controls by adding a variable measures field to the same sheets we just created.

To start with create a new Parameter and fill it with different measure names you want to see, I will call this [Measures Parameter]

Sum(Sales) ($)

Avg(Sales) ($)

Sum(Profit) ($)

Avg(Profit) ($)

Profit Ratio (%)

NOTE – the extra details I am using in the names, this will come in useful later on…

Next create a new calculated field and use the following CASE statement, I will call this [Sheet Measure]

CASE [Measures Parameter]

WHEN “Sum(Sales) ($)” THEN SUM([Sales])

WHEN “Avg(Sales) ($)” THEN AVG([Sales])

WHEN “Sum(Profit) ($)” THEN SUM([Profit])

WHEN “Avg(Profit) ($)” THEN AVG([Profit])

WHEN “Profit Ratio (%)” THEN SUM([Profit])/SUM([Sales])*100

END

NOTE – Make sure the Text matches exactly or it will not work.

Now go to one of your new sheets and replace the Sum([Sales]) with [Sheet Measure] and show [Measures Parameter] on the sheet so you can play with the controls. Pretty cool!

Now earlier I mentioned the extra detail in the names of each [Measures Parameter]. This was because I want to show the end user what they are looking at. Drag the [Measures Parameter] onto the Columns shelf… you will now see the measure being used actually on the sheet. With a couple of tweaks to hide the headers we don’t want it should look like this.

I now know that I am looking at the Sum of Sales in Dollars and it will change as I change the [Measures Parameter].

Repeat this update to all the new sheets using the new Measures Parameter and the new Sheet Measure. Once you have done that then add them onto a new dashboard and you should get something like this (there are tabs at the top to see my Method 2).

var divElement = document.getElementById(‘viz1563537294238’); var vizElement = divElement.getElementsByTagName(‘object’)[0]; vizElement.style.width=’800px’;vizElement.style.height=’650px’; var scriptElement = document.createElement(‘script’); scriptElement.src = ‘https://public.tableau.com/javascripts/api/viz_v1.js’; vizElement.parentNode.insertBefore(scriptElement, vizElement);

I hope you enjoyed that and please let me know if you have any comments.

Until next time !

Comments

Thanks Carl for a great tip!

What he suggests is that you can remove the ‘exact spelling and spaces’ issue by just using numbers in the value section of the Parameter. This would look like the below and my tip would be if you are updating a parameter then copy the value first before replacing it as the display name will be updated at the same time.

Then you can make a Calculated measure like the one below which is a lot easier to get right first time 🙂

CASE [Measures Parameter (copy)]
WHEN “1” THEN SUM([Sales])
WHEN “2” THEN AVG([Sales])
WHEN “3” THEN SUM([Profit])
WHEN “4” THEN AVG([Profit])
WHEN “5” THEN SUM([Profit])/SUM([Sales])*100
END