For #WOW2025 Week 25, Kyle challenged us to make use of Set Actions to recreate a viz where the ‘viz in tooltip’ updates based on the option the user interacts with on the base viz.
Modelling the data
The excel file provided contained 3 sheets which needed to be combined to use for this challenge. My data model looks like this
Attendance is related to Divisions on the fields Tm = Team
Attendance is also related to Record on the fields Tm = Tm.
Building the Base Bar Chart
On a new sheet, add Division to Rows and Attend/G to Columns changing the aggregation to AVG.Sort the data descending.
Change the Colour of the bars to dark grey and show mark labels to display the average attendance per game value. Add W-L% and Est.Payroll fields to the Tooltip shelf and set both to be AVG. Format Est. Payroll to be $ with 0dp. Format W-L% to be formatted to 3dp, but then adjust again and use a custom format to remove the leading 0 (,##.000;-#,##.000)
Format the label to be bold and to match mark colour. Format the row labels, remove the row label heading (right click > hide field labels for rows). Hide the axis and remove all gridlines, axis rulers etc. Update the viz title and name the sheet Bar-Division or similar.
Build the Scatter Plot
On a new sheet add W-L% to Columns and Attend/G to Rows, setting both the use an AVG aggregation, and then add Tm (from the Attendance table) to Detail. Adjust the W-L% axis so it doesn’t always include 0 (right click axis > edit axis> uncheck include zero). Adjust the title of the axis too. Adjust the title of the Attend/G axis too. Change the mark type to circle and increase the size.
We need the chart to show a difference between the marks related to a selected Division and those which aren’t. Create a set from the Division field (right click the field > create > and select NL West.
Add Division Set to Colour and adjust accordingly. Add a dark grey border to the circles. Remove all gridlines and name the sheet Scatter or similar.
Build the Attendance by Team Bar Chart
On a new sheet, add Tm (from the Attendance table) to Rows and Attend/G to Columns, setting the aggregation to AVG. Sort descending. Add Division Set to Colour.
Create a new field
Label Attendance
IF [Division Set] THEN [Attend/G] END
and add to the Label shelf. Format the label so it is bold and set to match mark colour. Hide the row label heading and the axis. Hide all gridlines, zero lines et. Name the sheet Bar-Team or similar.
Adding the Viz in Tooltip
Navigate back to the Bar-Division sheet. Update the Tooltip to reference the Division and the other top level measures. I used a double tab between the headings on one line and again on the values on the next line to make the information line up on hover (even though they look misaligned on the tooltip dialog).
To add each sheet to the tooltip, use Insert > Sheets button in toolbar and select the Scatter sheet and then press tab and select the Bar Team sheet.
Then adjust the text inserted so both filtering sections state filter=”None”. This stops the VIT filtering out by default all the data that isn’t associated to the selection you’re coming from. Adjust maxwidth and maxheight to 350 (I had it set larger, but then it didn’t display properly on Tableau Public, so had to adjust there.
Adding the interactivity
Create a dashboard and add the Bar-Divison sheet. At this point showing the tooltip on each bar will always show the information related to NL West since that was the option selected when we created the set. To fix this, create a new dashboard set action
Set Division
On hover of the Bar-Division sheet, target the Division Set, and assign values to the set. When the selection is cleared, keep set values. Only allow this to be run on single-select only
And now as you hover over different bars, the highlighted circles and bars in the viz in tooltip will change.
Sean’s #WOW2024 challenge this week was to visualise the comparisons between different entities within a limited space, by using a Viz In Tooltip (VIT) to provide additional information on hover.
Building the Treemap
Add Sales to Size, Category to Colour and Sub-Category to Detail to create the basic tree map. Tableau will automatically define the layout based on the space available, and you can’t control this, so don’t worry if it doesn’t match the final output.
Move Sub-Category from Detail to Label and add Sales to Label too. Format Sales to to be $ at 0dp, then adjust the Label as required.
Crete a new field
Rank
RANK(SUM([Sales]))
Format the field so it is formatted to be a whole number with a suffix of .)
Convert to discrete and add to the Detail shelf. Adjust the table calculation and verify it is computing by both Category and Sub-Category – this ranks every cell from 1 to 17 based on the Sales.
Adjust the Tooltip to just show the Category, Sub-Category and Sales data.
Name the sheet TreeMap.
Building the bar chart (the VIT)
On a new sheet add Sub-Category to Rows and Sales to Columns. Sort by Sales descending. Add Category to Colour and adjust the opacity to around 30%. Manually widen each row.
Add Sales and Sub-Category to Label and adjust to get the correct layout and then align left middle.
Add Rank to Rows after Sub-Category. Hide the Sub-Category field (uncheck show header).
We need to identify a row that has been ‘selected’ by the user. For this we need a parameter
pSelectedRank
Integer parameter defaulted to 0
Show the parameter on the page. We need to indicate which row is selected.
Selected Rank Indicator
IF [Rank] = [pSelectedRank] THEN ‘●’ ELSE ” END
Add this field to Rows before Rank, then update the pSelectedRank parameter to a valid number, eg 5.
Hide field labels for rows, make the Selected Rank Indicator and Rank columns narrower. Adjust alignment of columns and increase the font size of the Selected Rank Indicator column.
Hide the Sales axis, remove all gridlines, zero lines and row & column dividers. Format the Row Axis Ruler to be a black line.
We only want a subset of the rows to show – those that are 2 below and 2 above the selected rank. Create a new field
Rank in Range
([Rank] >= [pSelectedRank]-2) AND ([Rank]<= [pSelectedRank]+2)
Add this to Rows in front of Sub-Category. The bar chart will split.
We only want to see the ‘True’ rows. You can do this in 2 ways.
1 – Click on the word True and select Keep Only from the dialog box displayed. This will add Rank in Rangeto the Filter shelf
or
2 – right click on the section with the False records and select Hide from the context menu. The rows associated to False will disappear but won’t actually be filtered out of the view (this is what I did)
Hide the Rank in Range column from showing (uncheck show header). Name this sheet VIT or similar.
On the TreeMap sheet, update the Tooltip to show the VIT worksheet (via Insert > Sheets). Adjust the code snipped inserted so the filter property = “” (ie no filters are passed and the complete viz is displayed).
If you test it from the worksheet, and still have the pSelectedRank set to 5, you should see the same set of bars regardless of which part of the TreeMap you hover on.
Applying the filter
Create a dashboard of the required size and add the TreeMap sheet. Set the viz to fit entire view. It’s likely it will now rearrange itself to (nearly) match the solution.
Add a parameter dashboard action to set the selected rank
Set Rank
On hover of the TreeMap sheet, set the pSelectedRank parameter by passing in the value from the Rank field aggregated to nothing. When the selection is cleared, set to 0.
Hovering over the dashboard should now update the display in the VIT to ‘focus’ on whichever Sub-Category has been selected on the TreeMap.
This week’s challenge, set by Lorna, made use of the newly released ‘dotted line’ format in v2023.2, so you’ll need that version in order to achieve the dotted line that spans the 2 marks being compared. If you don’t have this version, you can still recreate the challenge, but you’ll just have a continuous line.
Building the calculations
This challenge involves table calculations, so my preferred startig point is to build out all the fields I need in a tabular format.
So start by adding Category to Columns and Order Date at the discrete (blue) week level to Rows. Add Sales to Text (I formatted Sales to $ with 0 dp, just for completeness).
Apply a Moving Average Quick Table Calculation to the Sales pill, then edit the table calculation to it is averaging over the previous 5 values (including the current value – ie 6 weeks in total), and it is computing by Order Date only.
Add another instance of Sales back into the table, so you can check the values.
The ‘moving average’ Sales pill is what will be used to plot the main line chart.
But we need to identify 2 points on the chart to compare – the values associated to a start date determined by the user clicking on the chart, and the values associated to an end date determined by the user hovering on the chart. We’ll make use of parameters to store these dates
pDateClick
date parameter defaulted to 27th Dec 2020
pDateHover
date parameter defaulted to 28 Nov 2011
We can then determine what the moving average Sales values were at these two dates
Sales to Compare
IF DATETRUNC(‘week’, MIN([Order Date])) = [pDateClick] OR DATETRUNC(‘week’, MIN([Order Date])) = [pDateHover] THEN WINDOW_AVG(SUM([Sales]), -5, 0) END
Add this to the table, and the values for the moving average will only be populated for the two dates stored in the parameters
This is the field we will use to plot the points to draw the lines with.
But we also need to work out the difference between these values so we can display the labels.
If the date matches the pDateHover (end) date then return the moving average and then spread that value over every row.
Add these into the table, and you can see how the table calculations are working
The moving avg value for the start date is displayed in every row against the Sales to Compare Start column, while the moving avg for the end date is displayed in every row for the Sales to Compare End column.
With these values now displayed on the same row, we can calculate
Difference
[Sales to Compare End]-[Sales to Compare Start]
formatted to $ with 0 dp
and
% Difference
[Difference]/[Sales to Compare Start]
formatted to a custom number format of ▲0.0%;▼0.0%;0.0%
Popping these into the tables, the values populate over every row, but when it comes to the label, we only want to show data against the comparison date, so I created
Label Difference
IF DATETRUNC(‘week’, MIN([Order Date])) = [pDateHover] THEN [Difference] END
formatted to $ with 0 dp, and
Label Difference %
IF DATETRUNC(‘week’, MIN([Order Date])) = [pDateHover] THEN [% Difference] END
formatted to a custom number format of ▲0.0%;▼0.0%;0.0%
With all these fields, we can now build the chart
Building the viz
On a new sheet, add Order Date set to the continuous (green) week level to Columns and Category to Rows. Add Sales to Rows and apply the moving average quick tablecalculation discussed above, adjusting so it is computing over 5 previous weeks and by Order Date only
Add Category to Colour and adjust accordingly, then reduce the opacity to around 40%
Add pDateClick to Detail then add a reference line to the Order Date axis the references this field. Adjust the Label and Tooltip fields, and set the line format to be a thick grey line.
Format the reference line text so it is aligned top right.
Then add pDateHover to Detail and add another reference line, but this time set the line so it is formatted as a dashed line.
Next, add Sales to Compare to Rows. Adjust the colour of the associated marks cards so it is 100% opacity.
Right click on the Sales to Compare pill and Format. On the Pane tab on the left hand side, set the Marks property of the Special Values section to Hide (Connect Lines). Click on the Path shelf and choose the dashed display option.
Set the chart to be Dual axis and synchronise the axis. Remove Measure Names from the All marks card. Hide the right hand axis.
Add Label Difference and Label Difference % to the Label shelf. Set the font to match mark colour and adjust font size and positioning. I left aligned the font but added a couple of spaces so it wasn’t as close to the line. I also added a couple of extra carriage returns so the text shifted up too.
Finally adjust tooltips, remove column dividers and hide the Category column title. Adjust the axis titles.
Adding the interactivity
Add the sheet onto a dashboard, then add 2 parameter actions
Set Start on Click
On select of the Viz sheet, pass the Order Date (week) into the pDateClick parameter. Leave the current value when selection is cleared (ie when user clicks elsewhere in viz, but not on another mark).
and
Set Comparison on Hover
On select of the Viz sheet, pass the Order Date (week) into the pDateHover parameter. Leave the current value when selection is cleared (ie when user clicks elsewhere in viz, but not on another mark).
Erica provided a set action based challenge this week requiring us to drill down to 2 levels in a hierarchy. I know there’s been similar challenges to this in the past, but I resisted checking them out, and attempted to build from memory. I succeeded – yay! Let’s crack on…
Note – I used the v2022.1 Superstore data that I already had on my laptop so filtered to December 2022. I also connected to the provided tds file rather than the excel instance, as for some reason, the tds includes Manufacturer which the excel file doesn’t.
Creating the main viz
On a new sheet, add Category to Rows and Sales to Columns and display as a bar chart. Add Order Date to Filter and restrict to the latest month/year in the data you have (in my case Dec 2022).
Create a set based off of Category (right click the Category pill > create set) and select a single value (eg Furniture) to be in the set. A field called Category Set should now exist in the data pane.
What we need to do is create a 2nd level in the hierarchy that shows the sub-categories related to the Category in the set, otherwise just display the Category.
2nd Level
IF [Category Set] THEN [Sub-Category] ELSE [Category] END
Add 2nd Level to Rows. Then right click on the Category Set field in the data pane and select Show Set to display the set values in a control on the sheet.
If you manually change the options in the Category Set control, the values displayed in the 2nd Level column will change.
Change the sort of the 2nd Level pill to sort by Sales descending
Create a set based off the 2nd Level field (right click field > create set), and select a single option that is one of the Furniture sub categories (eg Bookcases). A field called 2nd Level Set will be added to the data pane.
What we now need to do is create a 3rd level in the hierarchy that either shows the Category, the Sub-Category or the Manufacturer depending on the options selected in the sets. This is the only field which will actually be displayed on screen.
Display
IF [Category Set] AND NOT([2nd Level Set]) THEN ‘- ‘ + [Sub-Category] ELSEIF [Category Set] AND [2nd Level Set] THEN ‘ — ‘ + [Manufacturer] ELSE [Category] END
If we have a value selected in the Category Set but no value selected in the 2nd Level Set then display the Sub-Category (with some additional text formatting to give the indented effect).
Else, if we have a value selected in the Category Set and a value selected in the 2nd Level Set, then display the Manufacturer (again with additional text formatting and spacing).
Otherwise, display the Category.
Add Display onto the Rows, and then right click 2nd Level Set and select Show Set. Apply a sort to the Display pill so it’s also sorting by Sales descending.
Test the functionality, by changing the options in the set controls. For representative testing, ensure only 1 option maximum is selected in each set, and if an option in the 2nd Level Set is chosen, ensure it’s a Sub-Category related to the value selected in the Category Set.
Once happy, revert the options back to Furniture and Bookcases.
Format the sheet, and adjust the Row Divider so the slider is at the 2nd mark, and set the Column Divider to none.
Remove all gridlines. Then uncheck Show Header against the Category and 2nd Level pills in Rows. And finally, click on the Display title at the top of the column and Hide field labels for Rows.
To colour the bars, we’re going to use similar logic to that used to determine what to display
Colour Bar
IF [Category Set] AND NOT([2nd Level Set]) THEN ‘Sub Category’ ELSEIF [Category Set] AND [2nd Level Set] THEN ‘Manufacturer’ ELSE ‘Category’ END
Add this to the Colour shelf and adjust the colours. Update the tooltip and widen the rows slightly.
We’ve got the viz, now to add the interactivity so the changes happen ‘on click’ rather than via the set controls we’ve displayed.
Adding the interactivity
Create a dashboard and add the viz. Uncheck the options selected in both sets, so no options are selected – this should collapse the viz to just show the 3 Category values. Delete the container that shows the set controls/colour legend.
Create a new set action as below
Category Selected
on select (single selection only) assign the values to the Category Set, and remove all values when selection is cleared.
Create a 2nd set action
SubCat Selected
on select (single selection only) assign the values to the 2nd Level Set, and remove all values when selection is cleared.
Start clicking around, and BOOM! you should get the desired behaviour (note, once you reach the Manufacturer level, you’ll need to click twice on a manufacturer to get the viz to completely reset.
Bonus – building the ‘header’
I deduced from hovering over Erica’s solution that the ‘header’ was made up of multiple sheets, all aligned in a single row in the dashboard. I did have a play to see if I could come up with a single-sheet option, and while I think there is an alternative, it wouldn’t necessarily display exactly as Erica had shown. So I went down the multiple sheet option too.
Firstly we need a sheet to simply display the text >> Category.
On a new sheet, double click in the space below the Detail shelf on the marks card and type the text ‘dummy’. Then drag the ‘dummy’ pill onto the Label shelf, which will automatically change to now be labelled Text. Click on the Text shelf, and the 3dots to open the Edit Label dialog.
Delete the ‘dummy’ field, and replace it with ˃˃ Category. HOWEVER the ˃ symbol isn’t the > character I typed from keyboard. I found the double >> wasn’t retained this way, which I’m putting down to some HTML/XML related encoding that isn’t being handled. Instead I copied the symbol from this page https://www.compart.com/en/unicode/U+02C3 and pasted it into the text window. I then formatted the font size and style and coloured it the relevant blue.
Next we want to create a sheet showing the Category selected. Click on your viz to add a Category to the set (eg click the Furniture bar).
On a new sheet, add Category Set to the Filter shelf (this by default filter to values IN the set). Then add Category to Text and update the Text field as below, adjusting the font size/colour etc as before.
Duplicate this sheet as we can use this as the basis of the >>Sub-Category display.
On the duplicated sheet, update the text field to contain ˃˃ Sub-Category, where again the > is the character pasted. Format/colour the text
Now click on your viz to add a Sub-Category into the 2nd Level Set (eg click the Chairs bar).
On a new sheet, add 2nd Level Set to the Filter shelf (this by default filter to values IN the set). Then add 2nd Level to Text and update the Text field as below, adjusting the font size/colour etc as before.
Finally, once again, duplicate this sheet. Then on the duplicated sheet replace the text with >> Manufacturer instead
Now you have all the components you need. Add all these sheets onto your dashboard using a horizontal container to keep them all together. Apply the grey background shading if required (you’ll need to format the background colour on each worksheet, as well as the various objects on the dashboard.
Sean Miller provided the challenge for this week, resurrecting a challenge originally set by Emma Whyte in 2017. Revisiting these older challenges is great fun, as often newer product features provide a different way of solving. For me, I also like the fact I know I’ve already solved it once, and have my own work to reference if I get stuck – ha ha!
Sean hinted that this wasn’t a challenge to ‘overthink’ – no table calcs or LoDs required. You need to be able to display average responses per question per university alongside the overall average response for the question. Simply filtering by university isn’t going to cut it, as the quick filter will immediately eliminate all the data that isn’t associated to the selected university, which means you can’t compute an ‘overall average’ without using LoDs.
The key to this challenge is to use a parameter to drive the University selection. Create this by right clicking on the University field -> Create > Parameter. This will create the parameter dialog box, prepopulated with all the university values. Set the default to University of Liverpool.
pUniversity
With this, we can now create calculated fields to store the values associated to the selected university only.
Note 1, there is already field called Sample Size in the data set. The actual name of this field is <space>Sample Size<space> which Tableau sees as a different name. In hindsight I should have just renamed the original field, so I could then have ‘Sample Size‘. Be mindful of this when I refer to the field later; unless I call it out, I’m referring to my version.
Note 2, I chose to apply the AVG aggregation within the calc rather than changing the default aggregation on the pill when added to the view. There was a reason I did this, but I can’t recall what it was, and think it wasn’t necessary in the end….
University Avg
AVG(IF [University] = [pUniversity ] THEN [% Agree] END)
formatted to percentage, 1 dp
We can also then define the overall average for comparison
Overall Avg
AVG([% Agree])
formatted to percentage, 1 dp
and with that can calculate the variance between the two
Delta
([University Avg]) – ([Overall Avg])
This is custom formatted to 0.00%▲;0.00%▼ (I use this site to get the arrow characters)
And then we need a field to define how the mark needs to be coloured
Colour
[Delta]>=0
We can put these all out in a view
Question Number (which I renamed to No) on Rows
Question Text on Rows
Sample Size on Rows (set to be a discrete blue pill)
University Avg on Rows (set to be discrete)
Overall Avg on Rows (set to be discrete)
Delta on Rows (set to be discrete)
University Avg on Columns (continuous green pill)
Change Mark Type to Circle
Add Colour to the Colour shelf and adjust
Now we need to work on adding the various lines and bands on the chart. This is all managed by adding reference lines (or bands).
Drag % Agree to the Detail shelf, and change to be AVG.
The drag the same field % Agree on Detail again, this time change to MIN. Repeat again, and change to MAX.
Right click on the University Avg axis > Add Reference Line. Create a line, per pane using the AVG(% Agree) field.
Add another reference line (by right clicking on the axis again). This time create a band that starts at MIN(% Agree) and ends at MAX(% Agree). Set the Fill colour to light grey.
We need to create some new fields for the quartile values.
Lower Quartile
PERCENTILE([% Agree],0.25)
Upper Quartile
PERCENTILE([% Agree],0.75)
Add both these fields to the Detail shelf again, then add another reference line (band) similar to that above, but referencing the quartile fields. Set the Fill colour to be a darker grey.
Adjust the formatting and set the tooltips and you’ve got the main chart…. well almost…
In the solution, the first column, the question no, is not labelled. I couldn’t figure out how to do this, which is why I relabelled to simply No. I tried various things, including using text boxes as column headings on the dashboard, but the layout just didn’t work.
BUT I’ve now found out how to do it… because I googled, and I didn’t yesterday when I was building 😦 Andy Kriebel explains it all here. He searches for a ‘zero width space’ character on this site , and then copies the resulting ‘character image’ displayed and pastes into the label of a calculated field. Watch the video to see it in action, but I’ve noted the steps here, just as much for my own benefit when I can’t remember what to do in future… I can see this type of feature cropping up often 🙂
The legend utilises a lot of the concepts above, but we don’t what the mark changing with each university selection. So let’s just hardcode
Legend Avg
AVG(IF [University] = ‘Middlesex University’ THEN [% Agree] END)
and we’ll need a dedicated field for the colour
Legend Colour
[Legend Avg] – [Overall Avg] >=0
The legend sheet can then be built just by plotting the Legend Avg pill on the Columns shelf, with a mark type of circle, the Legend Colour on the Colour shelf, and the same pills used in the reference lines above on the Detail shelf.
When adding the reference lines and bands this time, you will need to add labels and format their position.
The quartile band, also has dotted lines indicating the end of the band, which you can apply as part of the band properties. However the quartile band also has one label left aligned, while the other is right aligned. For a single reference band, the labels can either both be formatted left aligned or both right aligned. To resolve this, don’t add a label to the ‘band to’ section. Create another reference line for the Upper Quartile value, and you can then format the label of this independently.
My published via associated to this challenge is here.
My version based on the original challenge from 2017 is here. The requirement was a bit more complicated it would seem, and it looks like I utilised FIXED LODs quite heavily.
Lorna set a fun ‘create your own pizza’ challenge this week to demonstrate the ability to both add and remove items in a set via the use of Set Actions, a feature introduced in v2020.2 (so you’re going to need this to complete the task).
There’s essentially 5 components to this dashboard, which I’ll guide you through
The central bar chart
The graphical product type selector on the left
The list of selected products to the right
The actual vs budget bar at the top
An indicator of how much you’re over/under budget
Central Bar Chart
The essence of this is a simple Type, Product by Price bar chart, coloured by Type. Manually move the Type field so the ‘Size’ option is at the top.
To indicate if the product is vegetarian or not, we create
Veg Indicator
IF [Vegetarian] = ‘Yes’ THEN ‘●’ ELSE ” END
which we can add to the Row shelf, and format to be coloured green (I use this site to get the shapes).
The tick is used to indicate if the product has been added to the pizza or not. Selected items will be identified by the use of a set, so we need to create one. Right-click Product and Create->Set. Tick a few options.
And similar to the Veg Indicator we can build
Selected Product Indicator
IF [Selected Products] THEN ‘✔’ ELSE ” END
Add this to the Row shelf between Type and Product.
The order of the Products listed will change. I want it to be orderedalphabetically by Product within each Type. The quickest way to resolve this, was to duplicate the Product field, add it to the Rows between the Type and Selected Product Indicator pills, and then hide it.
The final requirement for this chart, is to highlight the selected products in a different shade of the ‘base’ colour.
Add Selected Products to the Detail shelf, then change the … detail icon to the left of the pill to the colour icon. This will mean there are 2 pills on the Colour shelf, and the colour legend will change. Adjust to suit
The chart just then needs formatting to add the Price to the Label of the bar, remove the row/column lines, hide the Type column, hide the field labels, hide the axis, and adjust the width of the columns.
Product Type Selector
Lorna provided the images needed for this, which need to be saved to a new folder in the Shapes directory of your My Tableau Repository. I called my new folder simply Custom.
Then on a new sheet, add Type to Rows, set mark type to Shape and add Type to shape too.
Hide the Type heading and remove the row lines.
Selected Products List
Add Type & Product to Rows and enter MIN(1) onto the Columns. Add Selected Products to the Filter shelf to restrict the list just to those in the set.
Add Type to Colour and Product to Label, changing the font to white and centring. Fix the axis from 0-1 and hide it. Hide the Type & Product columns.
Manually reorder the Type field to be listed Size -> Crust -> Sauce -> Toppings
Actual vs Budget Bar
The budget field can be changed by the user, so we need a parameter, Budget, for this, which is an integer parameter where the display is formatted to £ with 0dp, and defaulted to 15.
Create the bar by adding Price to Columns, Selected Products to Filters and Type to Colour. Reorder the items in the colour legend to get the colours displaying in the correct order with ‘Size’ on the left and ‘Toppings’ on the right.
Add the Budget parameter to the Detail shelf, then add a Reference Line which refers to the Budget. Adjust the label displayed to be custom as shown below, and format the thickness and colour of the line to suit
Format the reference line so the text is displayed at the top right, and edit the font & size of the label displayed.
Then make the row height of the chart taller, but adjust the size of the bar to be narrower. The reference line spans the whole height of the row, so reducing the height of the bar itself provides space for the label to display without overlapping the bar.
Hide the axis and remove gridlines etc.
Finally we need to display the total price of the products selected. We can’t just add Price to Label as there are multiple segments on the bar, and this will display a Price per Type.
Instead we need a new field
Total Price Selected Products
{FIXED [Selected Products]:SUM([Price])}
Since this chart is filtered by Selected Products = True, this returns the total price of all the items in the set (selected). Add this to the Columns shelf and make dual axis & synchronise axis.
Readjust the mark types, so the Price marks card is back to a bar, and the Total Price Selected Products is Text. Remove the Measure Names pill from the Colour shelf of both cards (the dual axis will have automatically added it), and also remove the Type pill from the Colour shelf of the Total Price Selected Products card. Instead, add Total Price Selected Products to the Text shelf of this card.
The text will overlap the bar whether you align left , middle or right. The trick is to add some spaces in front of the text and then align right.
Hide the axis.
Over / Under Budget Indicator
This is just a text display, but a few calculated fields are needed so the text can be coloured differently depending on whether it’s over or under.
First we need to know the difference from budget
Diff from Budget
[Budget]-[Total Price Selected Products]
Text Budget Diff Over
IF [Diff From Budget] < 0 THEN ABS([Diff From Budget]) END
This will just display the difference if the value is negative, but the ABS function will return the number as a +ve. This field should be formatted to £ with 2 dp.
Text Budget Diff Under
IF [Diff From Budget] >= 0 THEN [Diff From Budget] END
This will just display the difference if the value is positive. Format to £, 2 dp.
Only one of these fields will ever hold a value at any time.
Similarly we need fields to show the text to display.
Over Budget Text
IF [Diff From Budget] < 0 THEN ‘Over Budget’ END
Under Budget Text
IF [Diff From Budget] >= 0 THEN ‘Under Budget’ END
On a new sheet, add Selected Products to the Filter shelf, then add all four of the above fields to the Text shelf.
Adjust the order and colour of the fields in the Label editor, setting the fields related to being ‘over budget’ to red and the ‘under budget’ fields to green.
Now we’ve got all the building blocks, we can put it all on a dashboard.
Building the dashboard interactivity
Add all the sheets in their various locations. Most were tiled, except for the Budget parameter and the Over/Under Budget Indicator sheet, which I floated.
Filter the bar chart
Add a dashboard Filter Action to filter from the Type Selector sheet to the Product Bar Chart on select, filtering on the Type field only.
Add / Remove from Set
Add a dashboard Set Action to the Chart sheet that runs from the Menu, and adds the selected item to the Selected Products set.
Create another Set Action on the same chart, which removes values instead.
And that’s the main crux of the challenge. The only addition is ‘nice’ feature to avoid the item selected from being shown as selected (ie fading out all the other items in the chart).
I reverted to the trusty true = false dashboard action which I applied to the Selector chart and the Selected Items chart.
This involves creating 2 new fields True = True and False = False and adding these to the Detail shelf of the relevant chart.
A dashboard Filter action is then added which targets itself using the fields True = False
However, when I tried to add this same feature to the main bar chart itself, the bar chart stopped working as expected. I think there was conflict between there being 2 filter actions on the same chart. I have to admit, this is where I did check Lorna’s solution, as this was a small feature that really bothered me, and I didn’t want to publish without it.
It turns out she simply used a highlight action to resolve this
I vaguely recall using this sometime ago, but the ‘true=false’ concept has become so ingrained as my ‘go to’ method, that I struggled to think of this.
And that should be it. You now have the tools to customise your own pizza 🙂
Donna + DataViz 