Can you build a funnel chart?

Donna ColesLeave a comment

For my first #WOW2023 challenge, as an ‘official’ part of the #WOW crew, I challenged everyone to build this funnel chart. Inspiration came from a colleague who posed me the challenge of building something similar, ensuring the bars were a single entity when clicked on. In that example we’d just used Superstore data, but it wasn’t really reflective of how a funnel would typically work, so I sourced some dummy sales opportunity data and made the challenge more relevant to a real business scenario.

Building the Funnel Chart

I saw this as a table calculation exercise, though I have subsequently seen solutions that manage it all through LODs. I’ll obviously blog my solution.

Let’s start by getting the core data into a table, so we can see what we’re aiming for

The total value of all the Opportunities is $91.4M. Based on the sales pipeline process and the assumptions documented in the requirements, all of that value will have passed through the first stage – Prospecting.

There is $6.1M still sitting in Prospecting, so when we move onto the next stage, stage 2 -Qualification, the total amount that has passed through will be $91.4M – $6.1M = $85.3M

As there is $6.5M sitting in Qualification, as we move to stage 3, the total amount that has passed through will be $85.3M – $6.5M = $78.7M. A similar pattern repeats for Stage 4, but for Stage 5, Closed Won, the value that has ‘passed’ through that stage, is equivalent to itself.

If we amend the Value field to be a Running Total quick table calculation (and add Value back into the view too), we get

The Running Sum is computing down the table , with the value in each row being the sum of the preceding rows. But this is opposite to what we want. We want Stage 1 to have the biggest value, the total, and the values to get smaller as you look down the table.

To resolve this, edit the table calculation and change the Sort order to custom sort by Minimum Stage No Descending

This results in the running total to sum from the last row (Stage 6) up to the first row (Stage 1), and you can see the value against Stage 1 matches the total for the Value column.

However, this still isn’t quite what we need. While we need to consider the amount for Closed Lost in stages 1-4, we shouldn’t be including it in stage 5, Closed Won.

While excluding the Stage 6 from the view (Stage No on Filter shelf set to Exclude 6) , gives the correct value for Stage 5, we’ve now lost the amount for Closed Lost in the other rows

So, we need some additional calculations to help resolve this.

Amount Lost

{FIXED : SUM(IF [Stage No]=6 THEN [Value] END)}

This just captures the amount of Stage 6 and ‘spreads it across every row of data.

Let’s now get our running total field, baked into it’s own calculation, as this will help with other calcs

Cumulative Value Per Stage

RUNNING_SUM(SUM([Value]))

Replace the Value table calc field with this one, ensuring the table calculation settings are identical to those described above.

Now let’s add the Amount Lost onto the Cumulative Value Per Stage unless we’re at Stage 5, Closed Won

Total Amount Per Stage Inc Lost

IF MIN([Stage No]) = 5 THEN [Cumulative Value Per Stage]
ELSE [Cumulative Value Per Stage] + SUM([Amount Lost]) END

Pop this into the table (checking the table calc settings are as expected) and the total amount now stored against Stage 1, is the total value of all the Opportunities.

Ok, so now we know the values, how do we plot this in such a way that it represents a funnel. I chose to do this by first working out the proportion of the total value that the ‘reverse’ cumulative value represented.

Total Value

{FIXED:SUM([Value])}

meant I could determine

Proportion of Total

[Total Amount Per Stage Inc Lost] / SUM([Total Value])

I formatted this to percentage with 0 dp.

Adding these into the table

If we plotted this information on a bar chart, we’d get this

but we need to ‘centre’ each bar in relation to the first bar to give us the funnel effect. This means we need to shift each ‘bar’ to the right, by half the amount that is the difference between 100% and the Proportion of Total.

Position to Plot

(1 – [Proportion of Total])/2

We can’t represent this with a actual bar chart, but instead we can use a Gannt chart.

  • Stage No to Rows
  • Stage No to Filter and exclude Stage 6
  • Stage to Detail
  • Position to Plot to Columns, adjusting the table calculation as previously described
  • Change mark type to Gantt bar
  • Add Proportion of Total to Size (and verify the table calc is set properly)

Hey presto! A funnel!

To finalise

  • add Stage to Label and align centrally. Make the font bold and match mark colour.
  • add Cumulative Value per Stage to Colour and reverse the colour range (via the Edit Colours dialog)
  • Widen each row a bit.
  • Fix the Position to Plot axis to start at 0 and end at 1 to ensure the funnel is centred exactly.
  • Add Value and Total Amount Per Stage Inc Lost to the Tooltip and adjust the tooltip text accordingly (don’t forget to sense check the table calc settings!).
  • Hide the Position to Plot axis, and the Stage No column. Remove all gridlines, zero lines, axis rulers /row/column banding etc.

Building the KPIs

We need a few calculated fields to store the required numbers

Won

{FIXED:SUM(IF [Stage No]=5 THEN [Value] END)} / [Total Value]

formatted to a percentage with 0 dp.

Lost

{FIXED:SUM(IF [Stage No]=6 THEN [Value] END)} / [Total Value]

formatted to a percentage with 0 dp.

Outstanding

1-([Lost] + [Won])

formatted to a percentage with 0dp.

On a new sheet add Measure Names to Columns and Measure Values to Text. Add Measure Names to Filter and select Total Value, Won, Lost, Outstanding. Manually re-order the columns.

Format Total Value to be a number displayed in millions with 1 decimal place & prefixed with $.

Add Measure Names to Text and adjust the text as required. Align the text to be centred.

Remove the row banding, and hide the column heading.

Then arrange the two sheets on the dashboard, ensuring both are set to fit entire view.

My published viz is here.

Hope you enjoyed this

Happy vizzin’!

Donna

Advertisement

Can you visualise survey data?

Donna Coles1 Comment

It was Sean’s first challenge of 2023, and he asked us to recreate this chart . Sean referred to it in the requirements as a floating bar chart, others may refer to it as a diverging bar chart or likert chart.

Modelling the data

Fake survey data was provided which was structured with each question in a column

The first thing we need to do is to pivot the data, so we have 1 row per question, per respondent. On the data source tab in Tableau Desktop, multi-select the question columns (Ctrl-click), then click the context menu for one of the selected columns and select Pivot

Due to the width of the screen and the number of columns, you may have to do this in multiple steps. Just multi-select the next set of columns, and select Add Data to Pivot from the context menu instead.

Once complete you should have a data set with 3 columns

Rename Pivot Field Names to Question and Pivot Field Values to Response.

Building the calculations

All the questions have 5 options to respond to ranging from Strongly Disagree -> Disagree -> Neutral -> Agree – > Strongly Agree. Sean hinted we would need to group the questions based on their sentiment. Some questions were worded in a positive way, which meant ‘agree’ responses could be taken as a ‘good thing’. While other questions were worded in a negative way, meaning agree’ responses were actually a ‘bad thing’.

So the first thing to do was to group the questions. I used the group functionality (right click on Question -> Create -> Group).

Sentiment

All the questions where I deemed an ‘agree’ answer was a favourable response to the product I grouped under Positive.

All the question where I deemed an ‘agree’ answer was a non-favourable response to the product I grouped under Negative.

I then wanted to bucket the responses into 3 groups – Disagree, Neutral and Agree – based on the combination of the actual Response, and the Sentiment grouping.

Response Group

IF [Sentiment] = ‘Positive’ THEN
IF CONTAINS([Response],’Disagree’) THEN ‘Disagree’
ELSEIF CONTAINS([Response], ‘Agree’) THEN ‘Agree’
ELSE ‘Neutral’
END
ELSEIF [Sentiment] = ‘Negative’ THEN
IF CONTAINS([Response],’Disagree’) THEN ‘Agree’
ELSEIF CONTAINS([Response], ‘Agree’) THEN ‘Disagree’
ELSE ‘Neutral’
END
END

With this I am essentially swapping disagree responses against questions of a negative sentiment into the agree/positive grouping and vice versa.

Finally I need another couple of calculations just to sense check were we are

Count Respondents

COUNTD([Id])

Question – Display

TRIM( SPLIT( [Question], “-“, -1 ) )

This removes the text that precedes the ‘-‘ from the Question string. I actually created this field using the Split functionality. Right click on Question -> Transform -> Custom Split. Use the separator – and split off the Last 1 columns

This will generate the field int he dimensions pane which I then renamed.

If we put all these out into a table, we can see how the data is shaping up

The next step is to work out the NPS values. When I’ve dealt with NPS in the past, I’ve referred to terms such as Promoters and Detractors, with an NPS score being computed as (Number of Promoters – Number of Detractors)/Total No of Respondents.

In this case a Promoter is anyone who is in the agree bucket.

Promoters

IF [Response Group]=’Agree’ THEN 1 ELSE 0 END

while a Detractor is anyone who is in the disagree bucket

Detractors

IF [Response Group]=’Disagree’ THEN 1 ELSE 0 END

The total number of respondents is

Total Respondents

{FIXED:COUNTD([Id])}

So I can now calculate

NPS

(SUM([Promoters])-SUM([Detractors]))/[Count Respondents]

Format this to percentage with 0 dp.

Let’s put this into another table so we can validate the data.

Ok, so this gives us the data required to plot the circle marks.

But we need to work out what’s need to plot the bars. And for this we need to know the respondents who are neither Promoters or Detractors.

Neutrals

IF [Response Group] = ‘Neutral’ THEN 1 ELSE 0 END

We are going to plot 2 bars for each row. 1 bar that represents the proportion of respondents who are in the positive side and 1 bar to represent the proportion of respondents who are in the negative side. The positive side mainly consist of the promoters, while the negative is the detractors. However, we need to consider the neutrals too, and we do this by halving the number and making half positive and half negative. Let’s see what I mean

# Respondents – Positive

(SUM([Promoters]) + (SUM([Neutrals])/2))/SUM([Total Respondents])

format this to percentage with 0 dp.

Similarly

# Respondents – Negative

-1 * ((SUM([Detractors]) + (SUM([Neutrals])/2))/SUM([Total Respondents]))

format this to percentage with 0 dp.

In this case the result is multipled by -1 as we want the bar to be plotted on the negative side of the axis.

Let’s pop these into the table too.

Ok. So now we have the core building blocks required to start building the viz.

Building the Likert Chart

Add Question – Display to Rows and # Respondents – Positive to Columns.

Click and drag # Respondents – Negative onto the canvas and drop the pill on the bottom axis when you see the 2 green columns icon appear.

This will automatically add the pill onto the same axis, and the view will update to use Measure Names and Measure Values

Remove Measure Names from the Rows and the bars will all appear on the same row. Sort the Question – Display field by NPS descending.

Add Response Group to the Colour shelf and adjust colours accordingly (using the Nuriel Stone palette). I also set the transparency to about 70%. You may need to reorder the values of Response Group – just do this manually in the colour legend if need be.

Increase the width of each row a bit, expand the Question – Display column, and remove the tooltips from displaying. Format the Question – Display column so it is aligned left and the font is a bit bolder/darker. Remove the Question – Display column heading (right click label > hide field labels for rows).

Format the axis to display as percentages to 0 dp, then edit the axis and

  • remove the title
  • fix the axis from -1.05 to 1.05
  • fix the tick marks to display every 0.5

Adjust the row banding to show, and remove any column/row dividers. Remove all gridlines. Adjust the column axis rulers and tick marks to be a solid dark line. Set the zero line for the columns to be a wider solid white line – it should just about be visible through the viz.

Now we need to label the agree and the disagree sections only. For this we need additional fields

Label – Positive

If MIN([Response Group]) = ‘Agree’ THEN [# Respondents – Positive] END

and

Label – Negative

If MIN([Response Group]) = ‘Disagree’ THEN [# Respondents – Negative] END

Add both these fields to the Label shelf and arrange side by side. Manually adjust the colour of the font to a dark grey.

This is the core design of a likert chart. It could be bult using dual axis too, but that wasn’t an option in this instance, as we need to add the additional NPS circles to the chart.

Adding the NPS circles

Add NPS to Columns. On the NPS marks card, remove all the fields, and change the mark type to circle. Add NPS to the Colour shelf, and adjust the colours so they range from the same yellow/green to blue/teal colours already used. The easiest way to do this is to click on the coloured square at each of the edit colour dialog and use the pick screen colour option to select the colours already used.

Set the colour to be 100% opacity and add a white border around the circles (via the Colour shelf)

Set the chart to be dual axis and synchronise the axis. Change the mark types back to bar and circle accordingly if they changed.

Now we can see some of the labels on the bar chart are positioned where the circles are/very close to them. Manually move those affected – click on the section of the bar to highlight it, then click on the label and when the cursor changes to a crosshatch, drag the label to where you want. Note – this isn’t something I would typically do if the data was to change behind this viz without any manual involvement.

Hide the top axis (uncheck show header) and remove all row/column dividers.

Now we just need to label the circles. I need additional fields for this. Firstly, the NPS field is on the wrong format – it’s actually stored as a decimal, formatted to a percentage. Secondly, I ended up with two label fields, due to the colouring. Sometimes when you add a label to a mark, the colour of the text may display black or white depending on the contrast with the underlying colour. Tableau automatically does this, and it isn’t something you can really control. As soon as you start manually adjusting any colours, it can be hard to get back to the right state. I fiddled around trying to get things to work properly for a while using the auto colouring (which involved rebuilding by starting with the NPS dot plot and then adding the bars), but it still wasn’t a ‘cast-iron’ solution. So I ended up creating

Label -NPS-Black

IF [NPS] * 100 <=70 THEN [NPS] *100 END

Label-NPS-White

IF [NPS]*100 >70 THEN [NPS]*100 END

I just chose an arbitrary ‘cut off’ for when the colour might change.

Add both these fields to the Label shelf of the NPS marks card, and arrange them side by side. Edit to font so the Label-NPS-White field is coloured with white font, and the other black. Set the alignment of the text to be middle centre. Adjust the size of the circles if required. Verify tooltips don’t exist on any marks.

Stopping the chart from being clicked

In the solution, if you try to click on the bar chart, you get no interaction – nothing highlights, like it does if you click on the axis or the questions. On a dashboard, this is managed by adding a floating blank object and carefully placing it over the top of the section you don’t want to be clickable.

And that should be it! My published viz is here.

I have to admit I did have a few false starts when building this out, and its highly probable this could have been created without all the fields I ended up with. It’s sometimes just the path your brain follows, and there’s nothing wrong with that!

Happy vizzin’!

Donna

Calculating Year-on-Year Percentage Change

Donna Coles1 Comment

In this week’s #WOW2023 challenge, Erica asked us to show the data for the selected year for a set of EU countries, but within the tooltip, provide additional information as to how the data compared to the same month in the previous year.

A note about the data

For this we needed to use the EU Superstore data set, a copy of which was provided via a link in the challenge page. Since part of validating whether I’ve done the right thing is to have the same numbers, I often tend to use any link to the data provided, rather than use any local references I may have to data sets (ie I have so many instances of Superstore on my local machine due to the number of Tableau instances I have installed). I did find however, that using the data from the link Erica provided, I ended up with a data set spanning 2015-2018 rather than 2016-2019. However I quickly saw that the numbers for each year had just been shifted by a year, so 2018 in Erica’s solution was equivalent to the 2017 data I had.

The viz is also just focussed on a subset of 6 countries. I chose to add a data source filter on Country to restrict the data to just those countries required (right click data source in the data pane -> Add data source filter).

Building out the required data calculations

The data will be controlled by two parameters relating to the Year and the Country

pYear

integer parameter, defaulted to 2017, displayed using the 2017 format (ie no thousand separators). 3 options available in a list : 2016,2017,2018

pCountry

string parameter defaulted to Germany. This is a list parameter and rather than type the values, I chose the option Add values from -> Country

We need to use the pYear parameter to determine the data we want to display, rather than simply apply a quick filter on Order Date, as we need to reference data from across years. Simply filtering by Order Date = 2017 will remove all the data except that for 2017, and so we won’t be able to work out the difference from the previous year. Instead we create

Sales Selected Year

ZN(IF [pYear] = YEAR([Order Date]) THEN [Sales] END)

Wrapping within ZN means the field will return 0 if there is no data.

Format this to € with 0 dp.

We can then also work out

Sales Prior Year

ZN(IF [pYear]-1 = YEAR([Order Date]) THEN [Sales] END)

which then means we can work out

Diff From PY

(SUM([Sales Selected Year]) – SUM([Sales Prior Year])) / SUM([Sales Prior Year])

custom format this to +0.0%;-0.0%;0.0%

This will display a positive change in the format +12.1%, a negative change as -12.1% and no change as 0.0%

Let’s pop all this information out in a tabular view along with the Country and Order Date to sense check the numbers

This gives us the core data to build the basic viz.

Core viz

Add Order Date at the Month date part level (blue pill) to Columns and Sales Selected Year to Rows and Country to Colour. Make sure it’s a line chart (use Show Me) if need be. Adjust the colours accordingly.

Amend the Order Date axis, so the month names are in the abbreviated format (right click on the bottom axis -> format)

Identifying the selected country

We need to change the colours of the lines to only show a coloured line for the selected country. For this we need

Is Selected Country

[Country]=[pCountry]

Add this field to the Detail shelf . Then click on the small icon to the left of the Is Selected Country pill, and select the Colour option.

This will mean that both Country and Is Selected Country are on the Colour shelf, and the colour legend will have changed to a combo of both pills

Move the Is Selected Country pill so it is positioned above the Country pill in the marks card section, and this will swap the order to be True | Country instead. Modify all the colours in the legend that start with False to be ‘grey’. Change the pCountry parameter and check the right colour combinations are displayed.

Change the Sort on the Is Selected Country pill so it is sorted by Data source order descending. This will ensure the coloured line is in front of the grey lines.

Adding the circles on the marks

We need a new field that will just identify the Sales for the selected country and selected year.

Sales Selected Year & Country

IF [Is Selected Country] AND [Year Order Date]=[pYear] THEN [Sales] END

Add this to Rows, then make the chart dual axis and synchronise the axis. Change the mark type of the Sales Selected Year & Country marks card to a circle, and adjust the Size to suit.

Finalising the line chart

Add Diff From PY onto the Tooltip shelf of the All marks card.

Create a new field

Month Order Date

DATENAME(‘month’, [Order Date])

and also add this to the Tooltip shelf. Adjust the tooltip to match the required formatting.

Hide the right hand axis (uncheck Show header).

Edit the left hand axis and delete the title, fix the axis from 0 to 50,000 and verify the axis ticks are displaying every 10,000 units.

Hide the 60 nulls indicator (right click -> hide indicator).

Remove the row & column dividers. Hide the Order Date column heading (right click -> hide field labels for columns)

Create the Country name for the heading

On a new sheet

  • Add Country to Rows
  • Add Is Selected Country to Filters and set to True
  • Add Country to Colour and then also add Is Selected Country to colour in the way described above.
  • Add Country to Label
  • Adjust the formatting of the Text so it is much larger font.

Hide the Country column (uncheck show header), and remove all row/column dividers. Ensure the tooltip won’t display.

Putting it all together

I used a horizontal container placed above the core viz. In the horizontal container I added blank objects, a text object, and the Country label sheet. I adjusted the size of the objects to leave space to then float the parameters. The parameters were resized to around 25 pixels so they just displayed the arrow part of the parameter. All this was a little bit of trial and error, and I did find that after publishing to Tableau Public, I had to adjust this section again using web edit.

My published viz is here.

Happy vizzin’!

Donna

Can you visualise orders?

Donna Coles2 Comments

For Luke’s first challenge of 2023, he asked us to recreate this visualisation which showed multiple chart types (donut charts and bar charts) within a tabular layout, all built on a single sheet.

As with most challenges, I’m going to first work on building out the calculations needed to provide the data being presented.

Building the calculations

Firstly we need to identify the number of orders, based on unique Order IDs

Total Orders

COUNTD([Order ID])

and then to get the average order value we need

Avg Order Size

SUM([Sales]) / [Total Orders]

format this to $ with 0 dp.

In order to build the donut chart, we will need two measures for each region; one which shows the % number of orders for that region, and one which shows the % number of orders across the remaining regions.

% Orders for Region

[Total Orders]/SUM({FIXED:COUNTD([Order ID])})

Format this to % with 0 dp

and then

% Orders for not Region

1 – [% Orders For Region]

format this to % with 0 dp.

Pop all these out in a table along with Sales formatted to $ with 0 dp, and Sort the Region field based on Total Orders descending.

Building the table view

Add Region to Rows and sort by Total Orders descending.

All the columns are going to be managed by what I refer to as a ‘fake axis’. Double click into the Columns shelf and type in MIN(0). This will create an axis.

Change the mark type to Text and add Total Orders to the Text shelf. Remove all the text from the Tooltip dialog box.

This is our 1st ‘column’ in the table.

Create another column by adding another instance of MIN(0) to Columns.

Change the mark type of this 2nd MIN(0) marks card to Pie Chart and remove Total Orders from the text shelf.

Add Measure Names to the Filters shelf, and ensure only % Orders for Region and % Orders for not Region are selected.

Add Measure Values to the Angle shelf of the pie chart, and add Measure Names to Colour. Adjust the colours to suit and ensure a white border is added (via the Colour shelf options). Again remove all the text in the Tooltip dialog.

Now add another instance of MIN(0) to Columns.

Change the mark type of this marks card to Circle and set the colour to white. Add % Orders for Region to the Label and align centre.

Now make this Min(0) pill to be dual axis and synchronise the axis. Reduce the size of the circle mark type marks card, so some of the pie chart is visible. Remove Tooltip text again.

Add another instance of MIN(0) for our ‘3rd’ column. Set the mark type to Text and add Sales to the Text shelf, and remove the tooltip text.

For the final column, add Avg Order Size to Columns. Change the mark type to bar. Reduce the size and adjust the Colour to suit. Add Avg Order Size to the Label and remove all tooltip text.

Remove all gridlines zero lines and column dividers. Add row dividers at the highest level so you have a divider per row. Uncheck Show Header on the first MIN(0) pill to hide the axes, and right click the Region column label and Hide field labels for rows.

This gives you the viz you need to add to a dashboard. Use a horizontal container with a blank and 4 text boxes positioned above the viz to provide the label heading to the columns.

OR…

you can do what I did, and having not noticed the comment about the textboxes, used further instances of MIN(0) to make every ‘column’ a dual axis so I could use the axis label for the secondary axis displayed at the top to provide the required labels.

I set the mark type of these additional MIN(0) axis to polygon, and ensured no other pills existed on the marks shelf. The tick marks on all the top and bottom axes had to be set to none, and the title on all the bottom axis had to be deleted.

The benefit of using the text boxes option is that you aren’t left with a ‘blank’ space underneath the chart. However wide your axis is at the top to display the labels, will be repeated as ‘blank space’ underneath the chart too (vote up this idea to prevent this).

My published viz is here.

Happy vizzin’!

Donna

Can you use Image Role & Dynamic Zone Visibility?

Donna Coles2 Comments

New year means only one thing – a change to the WorkoutWednesday hashtag! We’re now #WOW2023, and it was Lorna’s turn to kick the next year of challenges off with a focus on the latest Tableau features. This challenge uses the image role function introduced in 2022.4 and the dynamic role visibility feature introduced in 2022.3 (so you need to make sure you’re using at least 2022.4 to complete this).

Setting up the data source

Lorna provided an excel based data sheet which I downloaded. It contains several tabs, but the two you need to relate are Player Stats and Player Positions, relating as Lorna describes on both the Game ID and the Player Name

The data needs to be filtered to just the men’s competition, so I did this by adding a data source filter (right click on the data source -> Edit Data Source Filter) where Comp = Rugby League World Cup. Doing this meant I didn’t have to worry about adding fields to the Filter shelf at all.

Building the scatter plot

For the basic chart, simply add All Run Metres to Columns and Passes to Rows, then add Game ID and Name to the Detail shelf.

We need to identify which player and game is selected when a user clicks, so we need parameters to capture these.

pSelectedGameID

integer parameter defaulted to 0

pSelectedPlayer

string parameter defaulted to nothing/empty string/ ”

Show these parameters on the canvas, and then manually enter Brandon Smith and 21.

We now need calculated fields to reference the parameter values

Is Selected Player

[Name] = [pSelectedPlayer]

Is Selected Game

[Game ID] = [pSelectedGameID]

Both return a boolean True / False value, and we can then determine which combination of player/game each mark represents, which we need in order to colour the marks.

Colour : Scatter

IF [Is Selected Game] AND [Is Selected Player] THEN ‘Both’
ELSEIF [Is Selected Player] THEN ‘Player’
ELSEIF [Is Selected Game] THEN ‘Game’
ELSE ‘Neither’
END

Add this field to the Colour shelf, change the mark type to Circle and adjust the colours accordingly. Manually sort the order of the values so that the light grey ‘Neither’ option is listed last (which means it will always be underneath any other mark).

While not stated in the requirements, the marks for the selected player are larger than the others, so add Is Selected Player to the Size shelf, and adjust the size range to suit.

Adjust the tooltip and name the sheet Scatter.

Building the Bar Chart

On a new sheet, add Player Image and Game ID to Rows and All Run Metres and Passes to Columns. Add Is Selected Player to Filter and set to True. Edit the title of the sheet so it references the pSelectedPlayer parameter, and align centre.

Set the Player Image field to use the actual image of the player stored at the URL, by clicking on the ABC datatype icon to the left of the field in the data pane, and selecting Image Role -> URL

This will change the data type icon and update the view to show the actual player image

Add Is Selected Game to the Colour shelf on the All marks card and adjust accordingly. Remove the tooltips.

We need to show the axis titles at the top, rather than bottom. To do this add another instance of the All Run Metres field to the right of the existing one. Make dual axis and synchronise axis. Repeat with the Passes field by adding another instance to the right of the existing one and making dual axis again.

Set the mark type on the All marks card back to bar.

Right click on the All Run Metres bottom axis, remove the title, and set the tick marks to none. Repeat for the Passes bottom axis.

Right click on the All Run Metres top axis, and just set the tick marks to none. Again repeat for the Passes top axis.

Adjust the font of both axis title (right click axis -> format; I just set to Tableau Book) and then manually decrease the height of the axis.

Finally click the Label button on the All marks card and check the Show mark labels check box.

Then remove all column and row divider lines.

Adding the interactivity

On a dashboard, add a Vertical Layout Container, then add the bar chart into it and then add the scatter plot underneath. Remove the container that contains all the legends and parameters – we don’t need to show any of these. Hide the title of the scatter plot.

Add a dashboard parameter action to set the pSelectedPlayer parameter on select of a circle on the scatter plot. Set this parameter to pass the Name field into the parameter, and when clearing the selection, set the value to empty string/ nothing / ”

Select Player

Add another similar dashboard parameter action, which sets the pSelectedGame parameter in a similar way. This time, the Game ID field is passed into the parameter which is then set to 0 when the selection is cleared.

Select Game

If you test clicking on the scatter plot, you should now see the bar chart data disappear (although some white space about the height of the title remains) and you’ll also see that the circle selected is ‘highlighted’ compared to all the others.

To resolve the highlighting issue, navigate back to the scatter plot sheet, create a new calculated field called Dummy which just contains the string ‘Dummy’. Add this field to the Detail shelf.

Navigate back to the dashboard, and add a dashboard highlight action, that on select on the scatter plot, targets the same sheet on the same dashboard, but only focus on the Dummy field.

Deselect Marks

To make the whole section where the player bar chart is displayed, completely collapse, we need another parameter

pShowPlayerDetail

boolean parameter defaulted to false

We also need a boolean calculated field called

Show Player Detail

TRUE

Add this to the Detail shelf of the Scatter plot sheet.

Then navigate back to the dashboard, and create another parameter dashboard action, which on select of a circle on the scatter plot, sets the pShowPlayerDetail parameter based on the value from the Show Player Detail field. When the selection is cleared, reset the parameter to False.

Show Player Detail

Finally select the bar chart on the dashboard, then click the Layout tab on the left and side, and tick the Control visibility using value check box, and in the drop down select Parameters – > pShowPlayerDetail.

Now when a circle is clicked on, the bar chart section will completely disappear and the scatter plot will fill up the whole space.

Finalise the dashboard by adding the title and legend. My published viz is here.

Happy vizzin’!

Donna

Can you create a waffle chart?

Donna ColesLeave a comment

Kyle Yetter ended the #WOW2022 year with this challenge, to create a waffle chart. I haven’t built one for a while, so needed a quick google to refresh my memory.

You’ll need a ‘template’ data source which defines the grid pattern layout, and what each square represents. Every square represents a percentage point from 1% to 100%, and is positioned in a row numbered from 1 to 10 and a column numbered from 1 to 10. This information is stored in the ‘template’. You can build your own, or you can use the waffle_template.xlsx file I created that is stored here.

Connect to the Waffle_Template excel file and make Row and Column discrete dimensions (just drag them to above the line on the left hand data pane). Then add Row to Rows and Column to Columns and Percent to Text.

Building the waffle

Now connect to the Orders data from the SuperStoreSales data source. In that data source, create a new field

Consumer %

ROUND(SUM(IF [Segment]=’Consumer’ THEN [Sales] END) / SUM({FIXED:SUM([Sales])}),2)

If the Segment is Consumer, get the sum of Sales as a proportion of the total Sales. Round the result to 2 decimal places.

Create similar fields for the other segments

Corporate %

ROUND(SUM(IF [Segment]=’Corporate’ THEN [Sales] END) / SUM({FIXED:SUM([Sales])}),2)

Home Office %

ROUND(SUM(IF [Segment]=’Home Office’ THEN [Sales] END) / SUM({FIXED:SUM([Sales])}),2)

Then in the Waffle_Template datasource, create the following field

Colour : Consumer

[Orders (Sample – Superstore)].[Consumer %]>=SUM([Percent])

The above calculation will return true when the % Sales for Consumer is greater than or equal to the Percent value.

Repeat and create equivalent fields for the other segments

Colour : Corporate

[Orders (Sample – Superstore)].[Corporate %]>=SUM([Percent])

Colour : Home Office

[Orders (Sample – Superstore)].[Home Office %]>=SUM([Percent])

Add Colour : Consumer to the Colour shelf, and adjust colours. If you get any warning messages about blending, just acknowledge.

To make the blocks, double click in the Columns shelf and type in MIN(1). Change the mark type to bar. Edit the MIN(1) axis, and fix it from 0 to 1. Remove the Percent field from the Text shelf.

Set the row and column dividers to be thick white lines, and hide the column and row headings and axis (uncheck Show Header). Uncheck Show Tooltip on the Tooltip shelf.

Name this sheet Consumer.

Then duplicate this sheet, and replace the Colour : Consumer pill on the Colour shelf with the Colour : Corporate pill. Adjust colours to suit. Name the sheet Corporate.

Then duplicate again, and repeat the process for the Colour : Home Office pill, naming the sheet Home Office.

Building the KPIs

On a new sheet, using the Orders – SuperStoreSales data source add Segment to Columns.

Create a new field

Percent of Total

ROUND(SUM([Sales]) / TOTAL(SUM([Sales])),2)

format this to percent with 0 dp and add this to the Text shelf.

Create a new field

Segment UPPER

UPPER([Segment])

and add this to the Text shelf.

Format the text and align centrally

Remove the row dividers, the Segment column labels, and uncheck Show Tooltips from the Tooltip shelf. Name the sheet BANs.

Creating the dashboard

Use a horizontal container to position the three waffle charts in. Ensure each chart is set to fit entire view. Hide the titles, and from the context menu on the container, distribute the contents evenly. Use padding on the objects to create more white space.

Add the BANs object underneath, remove the title and set to fit entire view. Adjust the heights and widths as required to ensure the waffles are spaced evenly and are square, and the BANs line up underneath.

My published viz is here.

Happy vizzin’!

Donna

Let’s show quarterly sales with end-user flexibility

Donna ColesLeave a comment

It was Sean’s final #WOW2022 challenge of the year, and he set this task to provide alternative options for visualising time series across dimensions with high cardinality.

I had a play with Sean’s viz before I started tackling the challenge and noticed the following behaviour in addition to the requirements listed, which may or may not have been intentional.

When the cardinality of the dimension to display was more than 1 higher than the top n parameter, and the Show Others? option was set to Group By, the sales value to display was based on average sales rather than sum (as indicated in the requirements), and all the values not in the top n, were grouped under an ‘Other (Avg)’ label. But if the cardinality of the dimension was only 1 more than the top n parameter (so there was essentially only 1 value within ‘other’), then this value would display as itself (ie not labelled ‘other’) and the sales values would be summed rather than averaged (eg if the lines were to be split based on Ship Mode, and the top n was set to top 3 and Show Others? set to Group By, all four ship modes would display with the sum of sales rather than average).

This observation meant some of the calculations were slightly more complex than what I thought they would need to be initially, as I had to build in logic based on the number of values within a dimension.

So with that understood, let’s build the calcs…

Defining the calculations

Firstly we need some parameters

pDimensionToDisplay

String parameter defaulted to Subcategory, with the list of possible dimensions to split the chart by

pTop

integer parameter defaulted to 5

pShowOthers

I used an integer parameter with values 0 and 1 which I ‘aliased’ to the relevant values, defaulted to ‘Group Others’

I then needed to determine which dimension to be used based on the selected from the pDimensionToDisplay parameter.

DimensionSelected

CASE [pDimensionToDisplay]
WHEN ‘Subcategory’ THEN [Sub-Category]
WHEN ‘State’ THEN [State/Province]
WHEN ‘Ship Mode’ THEN [Ship Mode]
WHEN ‘Segment’ THEN [Segment]
ELSE ‘All’
END

From this, I could then use a Set to determine which of the values would be in the top n. Create a set of off DimensionSelected (right click -> Create -> Set)

Dimension Selected Set

select the Top tab, and create set based on the pTop parameter of Sales

To check this is working as expected, on a sheet, add DimensionSelected and Dimension Selected Set to Rows, add Sales to Text and sort by Sales descending. Show the pTop and pDimensionToDisplay parameters. Change the parameters and observe the results behave as expected.

Now we need to determine how many values are not in the set, ie, how many of the DimensionSelected values display as ‘Out’ in the above image.

Count Non-Set Items

{FIXED:COUNTD(IF NOT([Dimension Selected Set]) THEN [DimensionSelected] END)}

If the entry is not in the set, then return the entry, and the count the number of distinct entries we have. Using the FIXED level of detail calculation, wraps the value across every row.

Now this is understood, we need to work out whether we want to group the ‘non-set’ values under ‘other’

Dimension To Display

IF [pShowOthers]=0 AND NOT([Dimension Selected Set]) AND [Count Non-Set Items]>1 THEN ‘Other (Avg)’
ELSE [DimensionSelected]
END

If we’re opting to ‘group’ the values, and the entry isn’t in the set, and we’ve got more than 1 entry that isn’t in the set, then we can label as ‘Other (Avg)’, otherwise, we just want the dimension value.

We use similar logic to determine whether to display the SUM or AVG Sales.

Sales to Display

IF [pShowOthers]=0 AND SUM([Count Non-Set Items]) > 1 THEN AVG([Sales])
ELSE SUM([Sales]) END

Format this to $ with 0 dp.

We can then remove the DimensionSelected from our view, and test the behaviour, switching the pShowOthers parameter

Building the core viz

On a new sheet, add Order Date set to continuous quarters (green pill) to Columns and Sales To Display to Rows and Dimension To Display to Label. Show all the parameters.

Create a new field

Colour

[pShowOthers]=0 AND NOT([Dimension Selected Set]) AND [Count Non-Set Items]>1

add add to Colour shelf, and set colours accordingly.

Add Sub-Category, Ship Mode, State and Segment to the Filter shelf. Show them all, and then test the behaviour is as expected.

Extending the date axis

The date axis in the solution goes beyond 2022 Q4, and means the labels have a bit more ‘breathing space’. To extend the axis, click on Order Date pill in the Columns, and select Extend Date Range -> 6 months.

Then double click into the space next to the Sales To Display pill on the Rows and type MIN(0). This will create a second axis, and the date axis should now display to 2023 Q2

On the MIN(0) marks card, remove the Colour and Dimension To Display pills. Reduce the Size to as small as possible, and set the Colour opacity to 0%. Set the chart to dual axis, and synchronise the axis.

Finally tidy up the chart – hide the right hand axis, remove the title from the date axis, change the title on the left axis. Remove all row and column divider lines, but ensure the axis rulers are displayed. Title the viz referencing the pDimensionToDisplay parameter.

Building the dashboard

When putting the dashboard together, you need to ensure that the filters, parameters and main viz are all contained within a vertical container to ensure the viz ‘fills up’ the space when the controls section is collapsed.

The controls section itself is also a vertical container, which consists of 2 horizontal containers, one of which contains all the filters, and one which contains the parameters.

The layout tab shows how I managed this (I also like to rename the objects to keep better control).

The green Base vertical container is the first container. This essentially contains 3 ‘rows’ – the title, the User Controls vertical container and the viz itself.

The User Controls vertical container, then contains 2 rows itself – the Filters horizontal container (which has a pale grey background colour) and the Fine Tune horizontal container (which has a slightly darker grey background colour).

The User Controls vertical container is then set to ‘hide/show’ by selecting the whole container (click on the object in the item hierarchy on the left), and then selecting Add Show/Hide Button from the context menu. Adjust the button settings as required (I just altered the text displayed on hover) and then position accordingly.

It can take a bit of trial and error to get this right, and to get your containers working as expected. My published viz is here.

Happy vizzin’!

Donna

Interactive Proportional Brushing

Donna ColesLeave a comment

For her final challenge of #WOW2022, Erica set this interesting challenge; to show how the sales of the top n Products within each Sub-Category compared against the total sales. An added twist was to flip the display between actual sales, and % of total.

It did take me a short while to get my starting point. When I’ve worked with the concept of proportional brushing before, it’s typically involved set actions and one viz driving the interactivity on another viz via a dashboard action. I didn’t have this. I’ve obviously come up with a solution, but I’m not sure if it’s overly complicated… it doesn’t seem it, but for some reason I feel it could have been simpler than I’ve made it. Only time will tell as I check out other solutions.

My solution involves table calcs, so as I do with many challenges, I’ll start by building out all the data I need in tabular form.

Defining the calculated fields

Start off by adding Sub-Category to Rows, Product Name to Rows and Sales into Text and sort descending. We want to identify the Product Names in the top n, so lets first rank the products per sub-category. Create a calculated field

Sales Rank

RANK_UNIQUE(SUM([Sales]))

Make it a discrete field, then add it to Rows between Sub-Category and Product Name. Set the table calculation to compute using Product Name.

The rows should be numbered from 1 upwards, and restart at 1 when the Sub-Category changes

We want to get the sum of the sales for all those products in the top n. We’ll use a parameter to identify the n

pTop

Integer parameter defaulted to 10

Then we’ll use another table calculation to get the sum of the sales in that top n

Sales in Top n

WINDOW_SUM(IF [Sales Rank]<=[pTop] THEN SUM([Sales]) END)

If the Sales Rank is less than or equal to the top parameter, then get the Sales for that row, then sum all the sales values up together.

Show the parameter on screen, and add Sales in Top n into the table, setting the table calc to once again compute using by Product Name.

You should see that the sum of the sales for the rows marked 1-10 should equate to the value in the Sales in Top n column for the Sub-Category

We also need to get the total sales for each Sub-Category, which we can do with

Sub Cat Sales

{FIXED [Sub-Category]: SUM([Sales])}

Format to $ with 0 dp and displayed in k.

With this , we can also determine the proportion of sales

Top n % of Total

[Sales in Top n]/SUM([Sub Cat Sales])

Format this to a % with 0 dp.

Add both these fields to the table, making sure the table calcs are set to compute using Product Name.

So now we’ve got the base fields that form the building blocks for the viz. But we have this added functionality where we want to show either actual values (ie Sales in Top n and Sub Cat Sales), or we want to show the Top n % of Total compared to the ‘whole’ ie 1.

To manage this, we first need the parameter to control the decision

pView

integer list parameter set to 0 or 1, with appropriate display values. and defaulted to Sales Value

We can then build

Measure 1 to Display

CASE [pView]
WHEN 0 THEN [Sub Cat Sales]
ELSE 1
END

and

Measure 2 to Display

CASE [pView]
WHEN 0 THEN [Sales in Top n]
ELSE [Top n % of Total]
END

Pop these into the table and show the pView parameter. Test changing the parameter and see the results. Don’t worry that the % shows 0.. you can format the Measure 2 to Display field to show 2 dp if you want to see the data really changing.

Building the bar chart

On a new sheet, add Sub-Category to Rows, Product Name to Detail and Measure 1 to Display to Columns.

Drag the Measure 2 to Display field onto the Measue 1 to Display axis, and release when the 2 green columns symbol appears

Set the Measure 2 to Display field to compute using Product Name, then move Measure Names from Rows to Colour, and adjust the colours. Ensure Measure 2 to Display is listed first in the colour legend, so the marks are on top. Set stack marks to off (Analysis menu -> Stack marks -> off) to make the bars a single block, rather than being split per Product.

Switch the view parameter to change the display and verify it behaves as expected

Add the Top n % of Total field to Rows and change to be discrete (blue). Ensure the field is set to compute by Product Name still.

Create a new field

Label to Display

CASE [pView]
WHEN 0 THEN [Sub Cat Sales]
ELSE NULL
END

Format this to $ with 0 dp and displayed in k. Add this to the Label shelf

Add Sub Cat Sales to the Tooltip shelf and then adjust the tooltip to match.

The final step (other than formatting) is to apply the sorting. Create a new field

Sort

CASE [pView]
WHEN 0 THEN SUM([Sub Cat Sales]) * -1

ELSE [Top n % of Total] *-1
END

Make this field discrete and then add to Rows in front of the Sub-Category pill. You should now find the bars are being sorted descended depending on the view being displayed

Finally, tidy everything up – Uncheck show header on both the Sort and Measure Values pills. Hide field labels for rows to remove the column headings. Change the font size/format of the first two columns, and adjust the colour of the bar label. Remove all gridlines, column banding etc.

Finally add to a dashboard. I used a horizontal container, text boxes and padding to build up the parameter selections at the top. My published viz is here.

Happy vizzin’!

Donna

Can you make a pie chart?

Donna ColesLeave a comment

In his final challenge for 2022, Luke set this challenge asking us to recreate this pie chart. Although not mentioned in the challenge text, there was a hint on the splash page that map layers would be required.

I’ve only really used map layers in other #WOW challenges, and they actually involve maps. This challenge was obviously a bit different – utilising a functionality built for one purpose in an entirely different way. I remembered when map layers were first released there was a big buzz about the potential possibilities, and had seen some examples, but I’d never gotten round to trying out for myself, so this was the perfect opportunity (and one of the many plus points as to why I love doing #WOW challenges).

So where to start… good question. If you read up on the official Tableau KLs relating to map layers, it’s all about geography, and while the data source does have geographic data (State, City etc), they aren’t relevant in this case. In my ‘googling’ I found the following resources of use

The first 2 blogs helped me understand the need for the use of the MAKEPOINT function, Sam Parson’s Pies & Doughnuts viz helped me understand the calculation I’d need for the MAKEPOINT function, and the final blog post really helped with putting in all together.

Feel free to ignore the rest of this blog and use the above to help you out 🙂

Building the first map layer

The first step is to create the geometry field we need to base this off of.

Zero

MAKEPOINT(0,0)

Double click this field, and it will automatically add the point centrally onto a map with Longitude and Latitude fields automatically generated too.

This is a key step in getting things started and enabling the use of map layers which we’re going to utilise.

Add Segment to Colour and adjust the colours. Change the mark type to pie chart and add Sales to angle. Increase the size to be as large as possible.

However the size isn’t as big as we need. To increase further use Ctrl-Shift-B (windows) or Cmd-Shift-B (mac) to increase the size further (Ctrl-B / Cmd-B) to reduce. This trick I found in the Interworks blog above. All Tableau key shortcuts are listed here.

Add Segment to the Label shelf. This completes our lowest map layer.

Building the second map layer

Drag Zero onto the map canvas, and drop it over the Add a Marks layer section that displays. This will add a second marks card called Zero(2).

On the marks card that is named Zero, rename it to Outer Pie.

On the marks card named Zero (2) rename to White Circle. Change the mark type to Circle, change the Colour to white and increase the size to leave a narrow border of the coloured pie underneath.

Building the third map layer

Drag another instance of Zero onto the canvas and add another marks layer. Rename Zero (3) to Inner Pie. Change the mark type to Pie chart and add Segment to Colour and Sales to angle. Increase the Size so it’s just smaller than the white circle. Change the opacity of the colour to 70% and add a white border (Colour shelf).

Adding the labels in the pie chart

The simplest way to do this is just to label the inner pie chart with the required fields and then manually move the labels from outside the pie to the desired location. However if your data changed in some way, eg the proportion of the slices changed, the labels may not be where you wanted without further tweaks.

So instead I’ve added a 4th map layer.

Add Zero once again to the sheet and add a marks layer. Rename this marks card to Labels -Inner Pie. Change mark type to Pie chart and add Segment to Detail, Sales to Angle and Sales to Label. Create a new calculated field

Pct

SUM([Sales]) / TOTAL(SUM([Sales]))

Format to % with 0 dp and and add to Label. Adjust the fonts of the labels so the Sales value is larger.

Increase the size of the pie chart so the labels are positioned ‘nicely’ within the segments of the Inner pie

Reduce the opacity of the ‘label’ pie chart to 0% and set the mark layer to be disabled

Finishing up

Adjust the tooltips to display as required (you’ll need to add Pct to the Tooltip shelf on both the Outer and Inner Pie mark cards).

Then remove the map background via the Map menu -> Background Maps -> None. Hide all axis and remove all gridlines/zero lines/row/columns dividers. You should now be left with a ‘clean’ pie chart which can be added to a dashboard.

My published viz is here.

Happy vizzin’!

Donna

Can you do this in one dashboard?

Donna ColesLeave a comment

For this week’s #WorkoutWednesday challenge, Lorna revisited a challenge set by Ann Jackson in 2019 which I completed and blogged about here. In that challenge we were using new navigational features introduced in v2018.3. In this recreation, we’re making use of the dynamic zone visibility feature introduced in v2022.3 (so you’ll need at least that version of Tableau to progress).

I used 5 worksheets to build this viz and, as required, just 1 dashboard. 4 of the worksheets relate to the KPI blocks, and the other for the bar chart.

Building the KPI blocks

I started by creating new measures

Count Customers

CountD([Customer Name])

Count Orders

COUNTD([Order ID])

Count Cities

COUNTD([City])

Count Products

COUNTD([Product Name])

On a new sheet, add Count Customers to Text. Then add Measure Names to Filter and filter to just show the Count Customers measure. Then add Measure Values to Text and Measure Names to Text. Remove the original Count Customers from Text. Set the view to Entire View and then centre align and format the text. Set the mark type to Square and increase the Size as large as possible. Set the Colour to the relevant colour and adjust the font to match mark colour. Remove any tooltips from showing by unchecking show tooltips on the Tooltip shelf. Name the sheet Customers.

Repeat the process for creating sheets for City, Products and Orders. You may find you need to format some of the measures to have no decimal places.

Finally add Aliases to Measure Names to change the display of the measure from being Count XXXX to just XXXX (right click on Measure Names in the left hand data pane – > Aliases

Note you could possibly have named the measures just Orders, Cities etc to start with, though I think a measure called Orders already existed, so I chose this way to be consistent.

Building the bar chart

We’re going to use Dimension swapping for this chart – that is build a single chart but use a parameter to determine which dimension needs to be displayed.

pDimension

String parameter which is hardcoded initially to the word Customers.

Create a new field which will determine which dimension to show

Dimension to Display

CASE [pDimension]
WHEN ‘Orders’ THEN [Order ID]
WHEN ‘Customers’ THEN [Customer Name]
WHEN ‘Products’ THEN [Product Name]
WHEN ‘Cities’ THEN [City]
END

Note the names in the CASE statement to be stored in the parameter need to match the aliases we defined above.

On a new sheet, add Dimension to Display to Rows and Sales to Columns and sort by Sales descending. Add both Dimension to Display and Sales to Label. Format the label as required and align left. You may need to widen the rows to see the text.

Add pDimension to Colour and set the colour for the Customers dimension. Set the font to match mark colour.

Remove all axis and header columns (uncheck show header), all gridlines and row/column dividers. Add a title to the chart which references the pDimension field.

Now update the pDimension parameter to the word Orders, and set the colour. Repeat for Cities and Products.

Building the dashboard

Add the objects on to the dashaboard in such a way that you have a Vertical container that contains a Horizontal container with Customers and Orders side by side, then another Horizontal container underneath (still within the Vertical container), with City and Products side by side, and then finally add the Bar chart underneath. Set the 4 KPI block sheets to fit entire view and the bar chart to fit width. Try not to be tempted to adjust and heights of widths of objects manually on the dashboard, as that can affect things when we try to collapse later. You’ve probably got something like this:

Add a parameter action to drive the setting of the pDimension parameter

Select KPI

On select of any of the KPI block sheets, update the pDiemsion parameter passing through Measure Names. When selection is cleared, set the value to <empty string>/nothing

If you manually set the pDimension parameter to empty, your display should look like

Remove the titles from displaying from the KPI block sheets.

Hiding and showing the relevant sheets

To achieve this, we’re making use of Dynamic zone visibility functionality. For this we need some boolean fields to be created.

Crate a new calculated field

Dimension Selected

[pDimension]<>””

Similarly, create a new calculated field

Dimension Not Selected

[pDimension]=””

Back on the dashboard, select the Customers KPI block sheet (easiest way is to click on the object in the Item hierarchy pane at the bottom of the layout tab on the left hand side, as you may have trouble selecting on the dashboard itself due to the interactivity added). Once the sheet is selected it will have a grey border around it. On the layout tab on the left, select the Control visibility using value checkbox and select the Dimension Not Selected field.

Repeat this against the other 3 KPI block sheets.

Then for the bar sheet, do the same, except this time, select the Dimension Selected field instead. As soon as you do this, the bar should disappear, but reappear once you click on a KPI.

And that should be it. My published viz is here

Happy vizzin’!

Donna