Category: #WOW2022

Can you use Tableau to estimate Aaron Judge’s home run trajectories?

Donna ColesLeave a comment

It’s community month still for #WOW2022, and this week saw Samuel Epley set this challenge to visualise the home run trajectories of Aaron Judge.

I had a little mini-break to Rome this week, so was hoping I was going to be able to get this week’s challenge done and dusted on the Tuesday evening if it landed early enough, as I wasn’t going to be around.

It did land on the Tuesday for me, but wow! it was not going to be easy! I managed to build the KPIs & the scatter plots on the Tuesday evening, and knowing I didn’t have much time, just chose to use the Home Runs stats data set only. I knew these charts weren’t going to need any data densification, so found this approach simpler.

I’m afraid I’m still constrained by time at the moment, so this post isn’t going to be the detailed walkthrough you might usually expect – sorry! I’m just going to try to pull out key points from each chart.

KPIs

I built this on a single sheet, using Measure Names and Measure Values.

I used aliases on the Measure Names (right click -> Aliases) to change the label you can see displayed ie the Distance pill is aliased to ‘Average Distance’

I also custom formatted the various numbers and applied suffixes to display the unit of measure

Note – to To get the degree symbol, I typed Alt+ 0176

Scatter Plots

I built the Exit Velocity by Distance scatter plot first, and completed all the formatting & tooltips. Then I duplicated the sheet to form the basis of the other scatter plots, and just swapped the relevant pills as needed.

For the ball shape, I loaded the provided images as custom shapes into my shapes repository. I then just created the following calculated field to use as a discrete dimension I could add to the Shape shelf

Ball Shape

[HR Number]%9

It’s not as completely randomised as perhaps it should be, but it looks random enough on the display.

The Pitcher in the data is in the format <Surname>, <Forename>, but on the tooltip it needs to display as <Forename> <Surname>, so I just used a transformation on the Pitcher field to split the field based on the comma (right click Pitcher -> Transform -> Split). This automatically created 2 fields I could use on the Tooltip.

I also noticed a very subtle wording change in the tooltip based on whether the match was Home or Away. If Home, the tooltip read ‘New York Yankees vs. <Opposition>’ otherwise it read ‘New York Yankees at <Opposition>’. I used a calculated field for this logic

TOOLTIP: vs or at

IIF([Location]=’Home’,’vs.’, ‘at’)

The Trajectory Plot

OK, so this was the hardest part of this challenge, and mainly due to getting your head round the physics involved, as so many of the calculations are dependent on each other.

I’m generally pretty confident with my maths, but this was complex, especially with the force calculations for the y-axis. Samuel stated that both gravity and drag impacted the Y-axis calcs, but it wasn’t clear to me how both these forces should be applied (a bit of trial and error and I ended up adding them within the formula).

By the time I came to tackle this challenge, Samuel had already posted a video walkthrough, which can be viewed here and is another reason why I’m not going down to the nth degree in this post.

My suggestion is to watch Samuel’s video and/or feel free to download my workbook. I built my workbook independent of Samuel’s video, so there may be steps/calculations that differ.

However, I have tried to number my calculations in the order in which I created them, so you can hopefully follow the thought process. I have also left a CHK:Data sheet in the workbook, which I used to sense check what I was doing.

All the table calculations in the CHK:Data sheet are just set to the default ‘table down’ as I have filtered the sheet to a specific Home Run (HR Number = 1) only (ie I didn’t change any of the table calc settings as I added the pills to the sheet).

However, when you build the main trajectory chart, you have multiple HR Numbers in the view, so all the table calculations must be set so that calculations are only working for each HR Number. This means that any table calc (and any nested calculations) need to have all the fields except HR Number checked

When using the Pages shelf, which isn’t something I’ve ever really had to do before, you need to Show History and adjust the various settings to get the trail lines to show

To rotate the ball (the bonus option), you need another field to use on the Shape shelf. I had lost the will to live a bit by this point, so used the formula from my friend Rosario Gauna’s solution.

Rotation Shape

STR(IIF([14-Start Position Y m] <= 0, 0,
(MIN([Time Interval]) * 1000 / 25) % 9))

Note – when you add this to the Shape shelf, and select your baseball palette, just then use the Assign Palette button to automatically assign a ball to a number – this will get them into the correct order, without you having to do it one by one.

Finally, when adding the reference average lines, be sure to set the scope to per pane rather than table, otherwise you’ll end up with the wrong figures.

I think I’ve pretty much covered all the ‘little’ points that I came across that may trip you up, aside from all the tricky calcs of course!

My published workbook is here. I hope what I’ve written is enough for you to build it yourself. I think I’d still be here next year if I tried to do anything more fully! I’m off for a lie down now!

Happy vizzin’!

Donna

Advertisement

Can you do YoY comparisons?

Donna ColesLeave a comment

Community month for the #WOW2022 team continued this week, with Liam Huffman setting this challenge to build a year on year comparison chart with twist.

Typically when building YoY charts you compare the month from last year with the same month this year, or a week from last year with this year (eg week 3 2021 v week 3 2022), or a specific date last year with this year (eg 4th March 2021 with 4th March 2022). However for this challenge, the focus was on being able to compare based on equivalent weekdays (at least that’s what I understood from reading). By this I mean if 10 October 2022 is a Monday, that needs to be compared with the equivalent weekday in the previous year. 10 October 2021 was a Sunday, so we actually need to compare Monday 10 Oct 2022 with Monday 11 Oct 2021.

So that’s the direction I took with this challenge, but in doing so couldn’t get the numbers to exactly match with Liam’s solution. Unfortunately I just couldn’t get my head around Liam’s approach even after looking at it. So I’m blogging this based on my interpretation of the requirement, which may be flawed.

Baselining the Dates

When building YoY charts like this which need to be flexible based on the date part selected, you need to ‘baseline’ the dates; well that’s the term I use, others might refer to it as normalising. Ultimately you need to get the dates that span multiple years to all align to the same year, so you have a single x-axis that just spans a single year.

Now based on what I talked about above, its not just a case of changing the year of every Order Date to match for all the records, we need to find the equivalent weekday from the previous year and align that.

To do this I first created a parameter

pToday

date parameter defaulted to 10 Oct 2022

This is simply to provide a constant end point. In a business scenario when the data changes, any reference to pToday would just use TODAY().

I then created

1st Day of Current Year

DATE(DATETRUNC(‘year’, [pToday]))

This returns 1st Jan 2002

I then worked out what weekday this was

Weekday of 1st Day Current Year

//determine the day of the week this year started on, based on todays date
DATENAME(‘weekday’, [1st Day of Current Year])

This returns Saturday

I then wanted to find the date of the first Saturday in every year, but firstly I needed to determine the weekday for each Order Date

Weekday

DATENAME(‘weekday’, [Order Date])

Date of 1st weekday Per Year

//get the first date in each year which falls on the same weekday as the first day of the current year
//ie 1st Jan 2022 is a Saturday. This is day 0 for 2022. We need to find day 0 for every other
//year, which is the date of the first Saturday in that year

{FIXED YEAR([Order Date]): MIN(IF [Weekday]=[Weekday of 1st Day Current Year] THEN [Order Date] END)}

For each year, get all the dates which fall on a Saturday, then returned the earliest one of those.

Popping all this information out in a table, we can see that in 2019, the first Saturday in the year was 5th Jan 2019, so all the records for dates in 2019 are stamped with 5th Jan 2019.

This Date of 1st weekday Per Year is essentially day 0 for each year. We now need to record a number against each day in the year

Days From Date

//get the number of days from the date of 1st weekday per year
DATEDIFF(‘day’, [Date of 1st weekday Per Year], [Order Date])

And with this number, we can now record the equivalent date in 2022 against each date

Baseline Date

//need to normalise all dates for every year to the current year,
//ie Day 0 for every year = 01 Jan 2022

DATE(DATEADD(‘day’, [Days from Date], [1st Day of Current Year]))

Add these fields into the tabular view and you should hopefully see how this is working

Building the Line Chart

We need some additional parameters to help build the chart.

pDatePart

string parameter defaulted to Month, with 3 values listed as shown below

pStartDate

date parameter set to use the 1st Day of Current Year when the workbook opened, meaning current value was 1st Jan 2022

pEndDate

date parameter set to use the pToday parameter when the workbook opened, meaning current value was 10th Oct 2022

With the pDatePart field, we need to define the actual date we’re going to plot

Date to Plot

DATE(DATETRUNC([pDatePart], [Baseline Date]))

when this is set to week, the date for all days in the same week are set to the first date in the week. Similarly, when this is set to month, all dates in the same month are set to 1st of the month.

The data also needs to be filtered based on the start & end dates selected, so we need

Dates to Include

[Baseline Date]>= [pStartDate] AND [Baseline Date]<=[pEndDate]

Add this to the Filter shelf of the tabular view and set to True. Also add Date to Plot into the table, and show all the parameters. Change the dates and the date part field and familiarise yourself with how the parameters impact the data.

Now we’re happy we’ve got all the key fields we need, then create a new sheet, show the parameters and add Dates to Include = True to Filter.

Then add Date To Plot as an exact date continuous (green) pill to Columns and Sales to Rows.

Create

Order Date Year

YEAR([Order Date])

and add to Colour. Adjust the colours accordingly.

Format the Sales axis, so the numbers are displayed as numbers rolled up to thousands (k) with 0 dp. Edit the Date to Plot axis to remove the title, then format the dates displayed to be in dd mmm custom format. Remove all gridlines and zero lines. Only axis lines should be displayed. Update the title.

Add Date to Plot to Tooltip as a discrete attribute (blue pill). Add MIN(Order Date) to Tooltip too. Adjust tooltip wording to suit.

Building the bar chart

Add Order Date Year to Rows and Sales to Columns. Sort Order Date Year descending.

Add Order Date Year to Colour. Adjust the opacity to about 50% and add a border. Widen the rows. Add Sales to Label and format label to be $K to 1 dp.

Add another instance of Sales to Detail, then adjust to use a Quick Table Calculation of Percent Difference. Move this pill from Detail to Label. Adjust the table calculation so it is relative to Next rather than previous

Format the Sales field that has the table calc, so it is custom formatted to ↑ 0.0%; ↓ 0.0%

Modify the label so the % change is in ( ) .

Add MIN(0) to Columns (type directly in to the columns shelf). Remove the two Sales fields from the marks card, and add another instance of Order Date Year to the Label shelf. Adjust the Label so the font is larger, matches mark colour and is rotated.

Make the chart dual axis, and synchronise axis.

Turn off all tooltips, hide the Order Date Year column and both axis. Remove all gridlines and row & column borders. Add a title. Remove any instance of Measure Names that may have been added to the Colour shelf of either marks card.

And add Dates To Include = true to the Filter shelf.

Adding the interactivity

Add the sheets onto a dashboard and adjust the layout to match. I floated the parameters and positioned with some floating text boxes too, to get the desired display.

Add a highlight dashboard action

Highlight Trend

which on hover of the bar, highlights the trend line via the Order Date Year field only.

To manage the filtering of the bars, I decided to use a parameter action, by passing the date related to the point selected. For this I created

pDateHovered

I used a string parameter, so I had a value I could use to reset to

I then needed to create an additional field

Date to Plot String

STR([Date to Plot])

and I added this to the Detail shelf on the trend sheet. This needs to be set to be an attribute, so the lines remained joined up.

Additionally I needed

Filter On Hover

[pDateHovered] = ” OR [Date to Plot] = DATE([pDateHovered])

which I added to the Filter shelf of the Bar chart sheet and set to true.

Then back to the dashboard, create a parameter action

Filter Bars

which on hover on the trend chart, updates the pDateHovered parameter passing through the Date To Plot String field, and resets back to <empty string> when released.

And that should be it… my published viz is here.

Happy vizzin’!

Donna

Applying accessibility principals to data visualisation

Donna ColesLeave a comment

It’s Community month for #WOW2022, and so guest poster Collin Smith set this challenge to get us all thinking about how to make an accessible dashboard. There were 3 tiers of requirements, so lets start with Tier 1.

Building the charts

I used 5 sheets to create the core of this viz. One for the KPI title, 1 for each KPI and 1 for the bar chart. Typically I would have tried to build the KPI section all on a single sheet, but when I interacted with the solution to get a feel for how the tabbing worked (and given the hint in the requirements, not to use more than 5 sheets), I figured it would be necessary.

First up we need to define a parameter to use for the data selector. For this I created

Order Date Week

DATE(DATETRUNC(‘week’, [Order Date]))

and then adjusted the default sort order of the new field to be descending (right click pill -> default properties -> sort)

I then created the parameter to reference this

pSelectedWeek

date parameter where the values were added from the Order Date Week field. Default value set to 09 Aug 2021 (the data set didn’t match whatever Collin had used as my data went from 2018-2021, so I used a week in 2021 which was about equivalent to the week Collin had used, 10 Aug 2020).

The default sort on the field applied above, meant the dates got added in descending order.

I then created a field to filter the KPI charts

Is Selected Week?

[Order Date Week]=[pSelectedWeek]

this returns true or false.

On a new sheet, add Is Selected Week? to Filter and set to true. Then add Region to Filter and select East, West and South. Set the mark type to Polygon. Click the Tooltip button and clear the Show Tooltips option. Update the Title of the sheet as below, referencing pSelectedWeek and Region. Use Verdana 16 pt.

On a new sheet, again add Is Selected Week? to Filter and set to true. Then add Region to Filter and select East, West and South. Set the mark type to Polygon. Click the Tooltip button and clear the Show Tooltips option. Adjust the properties of the Sales field so it is formatted to $ with 0dp, then add Sales to the Detail shelf. Edit the title of the sheet to be where the word Sales is Verdana 16pt and the referenced Sales field is Verdana 32 pt. Note – this isn’t how I originally built this KPI, but I had to modify my approach to allow the screen reader to be able to read the values out (requirement Tier 3).

Repeat this process on a new sheet for the Profit KPI. And the similarly for the Profit Ratio KPI (note you’ll need to create the field first

Profit Ratio

SUM([Profit])/SUM([Sales])

and format to % to 1 dp.

To build the bar chart, we need a parameter to drive the metric selection.

pSelectMetric

I used an integer data type and assigned 1 to Sales and 2 to Profit, defaulted to 2.

From this I could create

Metris to Show

CASE [pSelectMetric]
WHEN 1 THEN [Sales]
WHEN 2 THEN [Profit]
END

On a new sheet I then again added Is Selected Week? to Filter (true) and Region to Filter (East, South, West). Then I added Product Name to Rows and Metric To Show to Columns and sorted by Metric To Show descending.

I then add Product Name to Filter and filtered by the Top 10 Metric To Show

Initially this may make everything disappear, but don’t panic. Click on the Is Selected Week? and Region fields in the Filter shelf and Add to Context.

Adding to context means the filters are applied before the top 10 is computed. Before, the top 10 filter was being applied first and then the other filters.

All of our sheets have 2 filters in common and we need to ensure they’re all working together. So now click on the Is Selected Week? filter and Apply to Worksheets > Selected Worksheets and select the 4 KPIS sheets you’ve built. Repat this with the Region filter. Only the bar chart should have the additional Product Name filter.

Now we need to format the look of the bar chart.

  • Add Profit Ratio to Colour
  • Add Metric To Show and Profit Ratio to Label. Format the label as below to Verdana 10pt.

  • Make each row a bit wider
  • Format the Product Name header for each row to be Verdana 12 pt.
  • Make the Product Name column a little wider
  • Add a dotted Row Divider to the Header level only

  • Add a thick black line to the Axis Ruler of the Rows

  • Hide the Metric To Show axis
  • Hide the Product Name column label (right click > hide field labels for rows)
  • Set Axis Ruler for Columns to None
  • Update the Tooltip using a mix of Verdana 16pt and 12pt

It should be looking something like…

Next, we need to give a title above the bar chart, which needs to be dynamic based on the metric selected. Create

Axis Label

CASE [pSelectMetric]
WHEN 1 THEN ‘Sales | Profit ratio’
WHEN 2 THEN ‘Profit | Profit ratio’
END

Add this to Columns.

Now to add the title. For this we need the name of the top Product Name. We need a new field for this

Top Product

WINDOW_MAX(IF SUM([Metric To Show]) = WINDOW_MAX(SUM([Metric To Show])) THEN MIN([Product Name]) END)

Let’s dissect this… WINDOW_MAX(SUM([Metric To Show])) returns the largest (maximum) value of Metric To Show that is being displayed. So we then test to find the row that has this value, and when a match is found we return the Product Name. This means we’ll get the Product Name for the first row of data, and NULL for the rest. The outer WINDOW_MAX is then finding the max value of the Product Name or Null, which again will be Product Name, and this value is then ‘spread’ across all rows. This means we have a single value for the top Product Name stored against every row, so when we reference it in the title we only have one value.

Add Top Product to Detail, then update the title as below using a mix of Verdana 16pt and 12 pt.

Finally, add the caption (Worksheet -> Add Caption) and adjust the text to match the required wording, referencing fields/parameters where appropriate.

Building the Dashboard and setting Focus (Tier 2)

Using a mixture of horizontal and vertical containers, I added all the necessary objects to the dashboard. I added the title and subtitle in a text box using a mix of Verdana 32pt and 16pt. I updated the titles and of format of the parameters and filters to use Verdana 12pt, and I added my standard footer. I then read through the community post Collin had provided to get an understanding on how to set the focus. As a result, I renamed all my containers and objects in the Item hierarchy.

Based on Collin’s viz, I needed to define the order of focus as

  • Filter-Week
  • Filter-Region
  • Filter-Metric
  • View-KPI Title
  • View-SalesKPI
  • View-ProfitKPI
  • View-ProfitRatioKPI
  • View-Bar

As per the instructions, this was going to require an edit to the XML of the workbook. So I did as advised and saved a copy of my workbook so I had a backup in case it all went wrong.

I then made a note on a piece of paper of each of the above fields and assigned a unique number against it ie Filter-Week 10, Filter-Region 20 etc.

I then opened up Notepad++ and opened the twb file in the editor.

I searched for dashboard and found the tag mentioned in the article and changed the relevant property to false

I then searched for zone, and went through changing the ID of every <zone> section. Anything that wasn’t part of my list above, I set to a unique sequential number from 100. When I hit one of the zones above, I set the value to the number I’d written down on paper. Once done I saved the file, closed notepad and reopened the workbook in Desktop. It loaded – phew! I then published to Tableau Public to test the tabbing functionality and it matched.

Testing the Screen Reader (Tier 3 requirement)

As I use a Windows machine, I used the inbuilt Narrator app. I used it against Collin’s solution to understand what I should expect. and then tested it against mine. Initially mine wouldn’t relay the ‘To open view data pane, click control, shift, enter’ instruction that was explicitly stated. I did some reading up (here) and verified the bar chart allowed the View Data control to show on the tooltip, I loaded the View data window in Desktop, and after publishing made sure the workbook was downloadable. However, I still couldn’t get this information to read out <shrug>. So I messaged Collin – what was I missing? The reply… add the required text in small white font, so it doesn’t display, but can be read! Bah! So simples in the end!

My published viz is here.

Happy vizzin’!

Donna

Where were COVID-19 cases most active?

Donna Coles2 Comments

In this week’s #WOW2022 instalment, Luke set this map-based challenge to visualise COVID-19 cases across the continental US states on 30 December 2021.

Each ‘pyramid’ on the map represents a county within a state, with the height and colour of the pyramid indicating the volume of cases recorded.

Luke provided a workbook with some pre-modelled data as a starting point, so I used that.

To familiarise myself with the data I built out a basic table, focusing on the State of Idaho (ID) only.

Clat10 and Clon10 are the latitude and longitude positions of the County and metrics.caseDensity is the key measure used to indicate the volume of cases.

To draw the pyramid, we need to plot 3 marks for each County, 1 for each point in the triangle. This means we need to densify the data so we have multiple rows for each County. If we add Point as a discrete dimension (blue pill) to the table, we can see we can generate up to 20 rows per County.

This is obviously excessive – we only need Points 1-3, so for simplicity, we can add Point to Filter and limit to just 1, 2 and 3. I added the filter by duplicating the Point pill from Rows (hold ctrl and then click and drag the pill from Rows to Filter – this added the filter as a discrete dimension and I could just select options 1, 2, 3).

With only 3 points, we can now build the lat and long coordinates for each point. I am assuming point 1 is the bottom left point, 2 is the top point, 3 is the bottom right.

Triangle Long

CASE [Point]
WHEN 1 THEN [Clon10] – 0.1
WHEN 2 THEN [Clon10]
WHEN 3 THEN [Clon10] + 0.1
END

As the requirements stated the base of pyramid was 0.2 longitude points wide, then the bottom left and bottom right points need to be 0.1 points to the left or right of the County longitude, while the peak is aligned centrally.

Triangle Lat

CASE [Point]
WHEN 1 THEN [Clat10]
WHEN 2 THEN [Clat10] + ([metrics.caseDensity]/60)
WHEN 3 THEN [Clat10]
END

The latitude of the left and right bottom points are aligned with the County latitude, while the height is an additional 1/60 of the metrics.caseDensity value above the County longitude.

Change both the Triangle Lat and Triangle Long fields to be of the Geographic role type of Latitude or Longitude accordingly (right click field -> Geographic Role -> Latitude/Longitude).

Adding these into the table, we can see how the coordinates are forming

So with this, we can now build the map

Add State to Filter and exclude AK, HI, PR (Alaska, Hawaii, Puerto Rico).

Double click Triangle Lat then double click Triangle Long to add them to the sheet – a map should automatically be presented.

Add County to Detail, and a mark should appear for every County.

Add Point to Detail and change to a dimension – there should now be 3 marks per County forming triangles. Zoom in to really see this.

Change the mark type to line and the points all join up to form the pyramid.

Add metrics.caseDensity to Colour, then edit the colour. Choose the red-gold colour palette, then click the Advanced button, and first change the centre to 75, then set the start & end value to 0 and 200.

Modify the Tooltip and reduce the Size of the line. Zoom back out

Finally amend the map background ( Map menu -> Background Layers). Uncheck base and land cover, check terrain and coastline. Uncheck Country/Region Names and State/Province Names.

Hide the null indicator and then place on a dashboard. Done!

My published viz is here.

Happy vizzin’!

Donna

Can you create a Waterfall Pipeline using Salesforce data?

Donna ColesLeave a comment

For the Salesforce Dreamforce (#DF22) conference, Lorna set this challenge based on using Salesforce data. You could access the data either by creating a Salesforce Developer account, or using the provided csv data set. I chose to use the latter.

I’m not overly familiar with the SF data, so it’s possible in the course of this blog, I may have missed a field in the data set that could have been used instead of whatever technique I describe. Feel free to let me know in the comments if this is the case.

Setting up the calcs

After connecting to the data, the first step was to amend the amount based on whether the Stage Name was ‘Closed Lost’ or not.

Revised Amount

IF [Stage Name]=’Closed Lost’ THEN -1 * [Amount] ELSE [Amount] END

I set the format of this field to be Currency £M with 1 dp.

Now the crux of the waterfall, is that the position of the mark that needs to be plotted it is based on the cumulative sum of the values displayed. So for this we need to create a table calculation

Revised Amount (Running Total)

RUNNING_SUM(SUM([Revised Amount]))

I set the format of this field to be Currency £M with 0 dp.

Let’s put the data into a table to see what’s going on.

The table calculation of the 2nd column (Revised Amount (Running Total)) is computing ‘down’ the table, so for this to work as we require the order of the rows is important. I just manually sorted by dragging each Stage Name into the relevant position. and then added a grand total.

With these 2 fields, we can now build the basic waterfall.

Build the Waterfall

I chose to duplicate the above sheet and then move the pills around as follows

  • Move Stage Name to Columns
  • Move Revised Amount (Running Total) to Rows. Amend the table calculation setting so it is set to explicitly compute using Stage Name.
  • Change the Mark Type to Gantt Bar
  • Move Revised Amount from Text to Size, then double click on the Revised Amount pill so it becomes editable and add * -1 to the end, to invert the value

  • Add Row Grand Total
  • Add Stage Name to Colour and manually adjust each colour accordingly

  • Add Revised Amount to Tooltip and adjust.
  • Edit Revised Amount (Running Total) axis (right click axis -> Edit Axis) and amend axis title
  • Format the Grand Total label (right click label -> Format) and amend says ‘Total’ and is bold.
  • Hide the Stage Name label at the top of the chart (right click label -> hide field names for columns)

Labelling the bars

Labelling the bars isn’t quite as simple as you think it might be, as the position of the label differs depending on whether the Revised Amount is positive (at the top) or negative (at the bottom).

So we need to create some new calculations

LABEL – +ve Revised Amount

IF SUM([Revised Amount])>=0 THEN SUM([Revised Amount])

formatted to £M to 1 decimal place

LABEL – -ve Revised Amount

IF SUM([Revised Amount])<0 THEN SUM([Revised Amount]) END

formatted to £M to 1 dp

Adding these to the tabular layout we had earlier you can see how these fields are behaving.

Switch back to the waterfall chart, and add LABEL – +ve Revised Amount to the Label shelf. Format the label to be smaller font (I chose 8pt), bold and explicitly aligned to the top.

Now duplicate the Revised Amount (Running Total) pill that is on Rows to add another instance directly next to it. I do this by holding down ctrl as I then click and drag the pill. This will create another axis.

On the second marks card, remove LABEL – +ve Revised Amount from Label and add LABEL – -ve Revised Amount instead. Adjust the label so it is aligned at the bottom instead.

Now make the chart dual axis and synchronise the axis.

Then hide the right hand axis, and remove all divider and gridlines.

Then add to the dashboard. I used a vertical layout container so I could add blanks of 1 pixel in height with a black background colour to present the this black lines separating the header and footer text.

My published viz is here.

Happy vizzin’!

Donna

Can you create a common starting point?

Donna ColesLeave a comment

Kyle set the challenge this week, revisiting his favourite topic – baseball. The aim was to build what I’ve often referred to as a ‘rocket chart’, as it charts progress from a single ‘launch’ date/point. However having had a quick google, I can’t see any other reference to this being used for this type of chart….no idea where it came from <shrug>.

Anyway, the requirement was to compare the profiles of when home runs (HRs) had been accumulated over the course of a player’s career, restricting to just the players who are in the all-time top 10. These players hadn’t necessarily played during the same years or even decades, so there was a need to baseline the information according to the days since they started. Kyle also threw in the requirement that this was to be an LoD based challenge only, with no use of table calculations.

Build the basic chart

As mentioned above, we first need to ascertain how many days have passed between when the player hit their first home run, and the subsequent dates. We use a FIXED LoD to work out the minimum date per player

Min Date Per Player

DATE({FIXED [Player] : MIN([Date])})

And with that we can the work out the number of days that have passed

Days Since Min Date

DATEDIFF(‘day’,[Min Date Per Player], [Date])

And with this, we can quickly build out the main crux of the chart. Add Days Since in Date to Columns, and change to be a continuous dimension. Add Career HR to Rows and amend the aggregation to use AVG rather than SUM, as I found there looked to be duplicate records for some dates for the same player. Add Player to Detail.

Colouring the lines

Kyle provided a custom colour palette to use based on the team colours of the player. I updated by preferences.tps file with this data, and closed and reopened Tableau Desktop to ensure it picked it up. For more information on working with custom colour palettes see this Tableau help article.

Along with the player colours, we also need to identify which player has been selected.

For that we need a parameter to define who the selected player is

pPlayer

string parameter using a List where the values are added from the Player dimension. this causes the default to be set to Albert Pujols.

Show the parameter on the display.

We can now create

Is Selected Player?

[Player] = [pPlayer]

which will return a boolen true/false.

Kyle stated that we should be able to set the colours without having to manually click against every Player|T or F combination.

Now I managed this when I first built my solution, but in writing this blog and trying to replicate the steps, I’m not getting the same behaviour. So I have managed to come up with another way. The gif below hopefully demonstrates, but I’ll list the steps too.

Move the Player pill from Detail onto Colour

Edit the Is Selected Player field to just return True (use // to just comment out the original calculation)

Add Is Selected Player to the Detail shelf, then click the detail icon to the left of the pill and change it to Colour. This is a way to get multiple pills on the Colour shelf. Dragging will just replace the field being used for colour.

The colour legend dialog box should display a list of <Player>, True entries (if the legend isn’t displaying go to Worksheet > Show Cards > Reset Cards – you may then have to add the parameter to the display again).

Edit the colour legend, select the MLB HR Top 10 colour palette and click Assign Palette. This will automatically assign the relevant colour to each entry, since they were added based on alphabetical order.

Re-edit the Is Selected Player field, so it is back to [Player] = [pPlayer].

The entries in the colour legend will now only list one <Player>, True entry and the rest all false.

Edit the colour legend, and multi-select (ctrl-click) all the False entries, and then select the lightest shade of grey from the Seattle Grays palette. This should give you the desired display.

Select Alan Rodriguez from the parameter control. Both Albert, False & Alan, True should now be coloured. Edit the colour legend again and manually set the Albert Pujols, False entry to the same grey shade.

Now if you select any other player, only 1 line should be coloured, and it should be coloured to the corresponding player’s colour.

Setting the Tooltip

Add Season HR to Tooltip and change the aggregation to AVG. Add Date to Tooltip too and set it to be an Attribute. Amend the tooltip accordingly.

Adding the highest season HR indicator

Firstly we need to determine what the maximum Season HR value is per player

Max Season HR Per Player

{FIXED [Player]: MAX([Season HR])}

With this, we then want to get the corresponding Career HR value for that same time.

Career HR | Max Season HR

IF [Season HR] = [Max Season HR Per Player] THEN [Career HR] END

Add this field to Rows and change the aggregation to Avg.

Set to Dual Axis, Synchronise Axis and then set the mark type to Circle. Adjust the size of the circle mark slightly if need be.

Labelling the lines

On the Line marks card, add Player and Career HR to the Label shelf. Adjust the aggregation of Career HR to Avg. Edit the label, so only line ends are labelled. Adjust the font size to something quite small, and set the colour to Match Mark Colour.

Finally remove all gridlines, row & column dividers, and hide the axis. Title the chart.

When added to a dashboard, I then used a floating text object for the introductory text and positioned the parameter as a floating object underneath the text.

My published viz is here.

Happy vizzin’!

Donna

Let’s build a Marrimeko Chart!

Donna Coles2 Comments

Sean set the challenge this week and went retro, revisiting a challenge from 2017 originally set by Emma Whyte to build a Marrimeko chart.

Hang on… a what chart? Marrimeko…???

A good place to understand what a Marrimeko chart actually is, is this blog post by Tableau Visionary & Ambassador, Jonathan Drummey. This leads onto this post which explains the steps to help build.

The main concept of this type of chart is to show part-to-whole relationships across two variables at once.  In the above for each job title, we have a split vertically based on proportion by gender; but the proportion of people with each job title is also being represented horizontally by the width of the ‘bars’. Both the x and the y axis are up to 100%.

I was actually around when the original challenge was set, so have my solution from then already on my Tableau Public profile. But it was a long time ago, with an older version of Tableau, so I built this from scratch (using the referenced blogs as a quick refresher). This blog will take you through the steps I took (which actually didn’t end up that different from the last time).

The data

The data set isn’t very large and contains all the information we will need, but not quite in the structure we might expect

The Sub-Type = Total field contains the % we will need to define how the width of the bars should be split (all the Total values add up to 100% or just about).

Whereas the combination of the Sub Types of Male and Female define how the height of the bars should be split (Male + Female = 100% for each Job Type).

So when we come to build, we ideally want to ‘filter out’ the Sub-Type = Total field, but we still need to retain this information to build the viz.

We need to get the % values associated to each Total row to be reflected against the Male/Female rows.

We’ll create an LoD for this

Job Type Percentage

{FIXED [Job Type]: SUM(IF [Sub-Type] = ‘Total’ THEN [Percentage]END )}

format this to % with 0 dp

If we pop the data out into a tabular format as below and add Sub-Type to the Filter shelf so it excludes the Total row, we can see we have essentially transposed the values which were stored against the Total row into it’s own column.

Building the Marrimeko chart

As stated above, the Job Type Percentage field defines the width of the bars we’ll be displaying. But before we can get to the point, we also need to define where on the x-axis each mark should be positioned. This too is based on Job Type Percentage, but is the cumulative value.

On a new sheet add Job Type to Rows, Filter by Sub-Type to exclude Total, and add Job Type Percentage to text. Sort by Job Type Percentage descending.

Now add a Running Total Quick Table Calculation to the Job Type Percentage field, and the cumulative values will display.

It is these values we need to plot on the x-axis.

Duplicate the sheet.

Edit the table calculation against the Job Type Percentage field, so that it is explicitly set to compute using Job Type. This is important to ensure the calculation is retained regardless as to where we put the pill on the canvas.

Now move this pill from the Text shelf to Columns, and change the mark type to Gantt Bar. Move Job Type from Rows to the Detail shelf. Reapply the Sort to the Job Type field so its sorting by Job Type Percentage descending

The markers for the Gantt should all be positioned at the correct position.

Now add Percentage to Rows and change the mark type to Bar.

Add Job Type Percentage to the Size shelf, then click on the shelf, and change to be Fixed and aligned Right.

Now add Sub-Type to Colour. Manually drag the values in the COlour legend to re-order (so Male is listed first) and adjust colours to suit.

Ta dah! The crux of the chart.

Now to add the ‘bells and whistles’.

The chart is labelled, but only for the Entry Job Type. We need a calculated field to manage this.

Label: Sub Type

IF [Job Type] = ‘Entry’ THEN [Sub-Type] END

Add this to the Label shelf, and set to Show Mark labels. If it doesn’t show (like it didn’t on mine), change the field to be an attribute (I need to do a dig on why this is required… I think it’s something to do with the table calcs….).

I tried to use the alignment setting of the label to set to ‘top left’, but while they would left align they wouldn’t move to the top. I manually moved them instead – simply click on the label and when the cursor changes to a cross, drag the label to the desired position.

Do this for both labels, and adjust the formatting of the label too (I set it to 12pt bold).

Remove all gridlines, axis lines, zero lines etc.

Add a 50% reference line on the y-axis. Right click on the Percentage axis ->Add Reference Line. Add a table level constant of 0.5 which is a thin dotted grey line that is almost invisible (due to the colour selected).

Hide the axes (uncheck show header).

The tooltip has a minor nuance in that it refers to ‘Women’ & Men rather than Male & Female, so we need a field for this

Gender

CASE [Sub-Type]
WHEN ‘Female’ THEN ‘Women’
WHEN ‘Male’ THEN ‘Men’
END

Add this to the Tooltip shelf and adjust the tooltip accordingly.

Labelling the axes

The final viz has labels on both the x and y axis, but these are all managed by text/image objects positioned ‘cleverly’ on the dashboard. It’s a bit of trial and error to get everything aligned as required.

To label the Job Type, I used a horizontal container, positioned beneath the chart, with several text objects, some of which had the text rotated.

The Equal Proportions and Less Equality text are both floating text objects. I saved an ‘arrow’ image from the internet and added a floating image object too.

My published viz is here.

Happy vizzin’!

Donna

Can you compare the latest month to the prior X months average?

Donna ColesLeave a comment

Lorna set this challenge this week to test some LoD fundamentals. My solution has a mix of LoDs and table calculations – there weren’t any ‘no table calcs’ allowed instructions, so I assumed they weren’t off limits and in my opinion, were the quickest method to achieve the line graph.

Note – For this challenge, I downloaded and installed the latest version of Tableau Desktop v2022.2.0 since Lorna was using the version of Superstore that came with it. The Region field in that dataset was set to a geographic role type. I built everything I describe below using the field fine, but when I extracted the data source at the end and ‘hid all unused fields’ before publishing, the Region field reported an error (pill went red, viz wouldn’t display). To resolve, I ended up removing the geographic role from the field and setting it just to be a string datatype. At this point I’m not sure if this an ‘unexpected feature’ in the new release…

Ok, let’s get on with the build.

Building the basic viz

I started by building out the basic bar & line chart. Add Region to Rows, Order Date as continuous month (green pill) to Columns and Sales to Rows. Change the mark type to bar, change the Size to manual and adjust.

Drag another copy of the Sales pill to Rows, so its next the other one. Click on the context menu of that 2nd pill, and select Quick Table Calculation -> Moving Average

Change the mark type of the 2nd Sales pill to line.

Now click on the context menu of the 2nd Sales pill again and Edit Table Calculation. Select the arrow next to the ‘Average, prev 2, next 0’ statement, and in the resulting dialog box, change the Previous values to 3 and uncheck the Current Value box

At this point you can verify whether the values match Lorna’s solution when set to 3 previous months.

But, we need to be able to alter the number of months the moving average is computing over. For that we need a parameter

pPriorMonths

integer parameter, default to 3, that ranges from 2 to 12 in steps of 1.

Then click on the 2nd Sales pill and hold down Shift, and drag the pill into the field pane on the left hand side. This will create a new field for you, based on the moving average table calculation. Edit the field. Rename it and amend it so it references the pPriorMonths parameter as below

Moving Avg Sales

WINDOW_AVG(SUM([Sales]), -1*[pPriorMonths], -1)

Adjust the tooltip for both the line and the bar (they do differ). Ignore the additional statement on the final bar for now.

Colour the line black and adjust size. Then make the chart dual axis and synchronise axis. Hide the right hand axis. Remove Measure Names from the Colour shelf of both marks cards.

Colouring the last bar

In order to colour the last bar in each row, we need 3 pieces of information – the value of Sales for the last month, the moving average value for the last month, and an indicator of whether one is bigger than the other. This is where the LoDs come in.

First up, lets work out the latest month in the dataset.

Latest Month

DATE(DATETRUNC(‘month’,{FIXED : MAX([Order Date])}))

finds the latest date in the whole dataset and truncates to the 1st of the month. Note, this works as there’s sales in the last month for all Regions, if there hadn’t been, the calculation would have needed to be amended to be FIXED by Region.

From this, we can get the Sales for that month for each Region

Latest Sales Per Region

{FIXED [Region] :SUM( IF DATETRUNC(‘month’, [Order Date]) = [Latest Month] THEN [Sales] END)}

To work out the value of the moving average sales in that last month, we want to sum the Sales for the relevant number of months prior to the last month, and divide by the number of months, so we have an average.

First let’s work out the month we’re going to be averaging from

Prior n Month

DATE(DATEADD(‘month’, (-1 * [pPriorMonths]),[Latest Month]))

This subtracts the relevant number of months from our Latest Month, so if the Latest Month is 01 Dec 2022 and we want to go back 3 months, we get 01 Sept 2022.

Avg Sales Last n Months

{FIXED [Region]:SUM( IF DATETRUNC(‘month’, [Order Date]) >= [Prior n Month] AND
[Order Date] < [Latest Month] THEN [Sales] END)} / [pPriorMonths]

So assuming we’re looking at prior 3 months, for each Region, if the Order Date is greater than or equal to 01 Sept 2022 and the Order Date is less than 1st Dec 2022, get me the Sales value, then Sum it all up and divide by 3.

And now we determine whether the sales is above or below the average

Latest Sales Above Avg

SUM([Latest Sales Per Region]) > SUM([Avg Sales Last n Months])

If you want to sense check the figures, and play with the previous months, then pop the data into a table as below

So now we’re happy with the core calculations, we just need a couple more to finalise the visualisation.

If we just dropped the Latest Sales Above Avg pill onto the Colour shelf of the bar chart, all the bars for every month would be coloured, since the calculation is FIXED at the Region level, and so the value is the same for all rows associated to the the Region. We don’t want that, so we need

Colour Bar

IF DATETRUNC(‘month’, MIN([Order Date])) = MIN([Latest Month]) THEN
[Latest Sales Above Avg]
END

If it’s latest month, then determine if we’re above or below. Note the MIN() is required as the Latest Sales Above Avg is an aggregated field so the other values need to be aggregated. MAX() or ATTR() would have worked just as well.

Add this field to the Colour shelf of the bar marks card and adjust accordingly.

Sorting the Tooltip for the last bar

The final bar has an additional piece of text on the tooltip indicating whether it was above or below the average. This is managed within it’s own calculated field.

Tooltip: above|below

IF DATETRUNC(‘month’ ,MIN([Order Date])) = MIN([Latest Month]) THEN
IF [Latest Sales Above Avg] THEN ‘The latest month was above the prior ‘ + STR([pPriorMonths]) + ‘ month sales average’
ELSE ‘The latest month was below the prior ‘ + STR([pPriorMonths]) + ‘ month sales average’
END
END

If it’s the latest month, then if the sales is above average, then output the string “The latest month was above the prior x month sales average” otherwise output the string “The latest month was below the prior x month sales average”.

Add this field onto the Tooltip shelf of the bar marks card, and amend the tooltip text to reference the field.

Finalise the chart by removing column banding, hiding field labels for rows, and hiding the ‘4 nulls’ indicator displayed bottom right.

Creating an Info icon

On a new sheet, double click into the space within the marks card that is beneath the Detail, Tooltip, Shape shelves, and type in any random string (”, or ‘info’ or ‘dummy’). Change the mark type to shape and select an appropriate shape. I happened to have some ? custom shapes, so used that rather than create a new one. For information on how to create custom shapes, see here. Amend the tooltip to the relevant text. When adding to the dashboard, this sheet was just ‘floated’ into the position I was afte. I removed the title and fit to entire view.

My published viz is here.

Happy vizzin’!

Donna

Can you build a dumbbell chart?

Donna ColesLeave a comment

Week 32 of #WOW2022 and Kyle set this challenge to build a dumbbell chart (also sometimes referred to as a barbell chart, a gap chart, a connected dot plot, a DNA chart). I first built one of these a long time ago, and have built many since, so chose not to refer to Ryan Sleeper’s blog Kyle so generously provided. I figured I’d have a go myself from memory. Looking back now, I actually started by going down the same route a Ryan, but then switched my approach in order to handle the tooltip requirements.

So here’s what I did.

Defining the calculations required

I’m going to start with these, as it’s then easier just to explain how everything then gets built. In reality, some of these calculations came along mid-build.

I decided to create explicit fields to store the Ownership values for the 2015 & 2019 years ie

Ownership 2015

IF [Year]=2015 THEN [Ownership] END

Ownership 2019

IF [Year]=2019 THEN [Ownership] END

Both these fields I formatted to % with 0dp.

Given these, I can then work out the difference

Difference

SUM([Ownership 2019])-SUM([Ownership 2015])

also formatted to % with 0dp

and finally, I can work out whether the change exceeds 20% or not

Difference>0.2

[Difference]>0.2

These are all the fields required 🙂

Building the Dot Plot

Add Age to Columns as a discrete dimension (blue pill) and Ownership 2015 to Rows. This will create a bar chart.

Then drag Ownership 2019 onto the canvas, and release your mouse at the point it is over the Ownership 2015 axis and the 2 green columns symbol appears.

This will have the effect of adding Measure Names and Measure Values to the view.

Change the mark type to Circle and move Measure Names from Columns onto Colour. Adjust colours accordingly.

Show mark labels and adjust so they are formatted middle centre, and are bold. Increase the size of the circles so the label text is completely within the circle, and the font colour should automatically adjust to white on the darker circles and black on the lighter ones.

Add Ownership 2015, Ownership 2019 and Difference onto the Tooltip shelf, and adjust the tooltip to suit. Note – I chose to adjust the wording of the tooltip to not assume there was always an increase. I used the word ‘change’ instead and as a result applied the custom formatting of â–²0%;â–¼0% to the Difference field that was on the Tooltip shelf.

So my tooltip was

which generated a tooltip that looked like

Connecting the dots

Add another instance of Measure Values to Rows alongside the existing one. This will generate a 2nd Measure Values marks card.

On that marks card, remove Measure Names from Colour and add Difference>0.2 to the Colour shelf instead, and adjust the colours.

Change the mark type to Line and add Measure Names to the Path shelf.

Move the Difference pill from the Tooltip shelf to the Label shelf. Edit the label controls, so the label only displays at the start of the line, which should make the label only show at the bottom. Adjust the label alignment so it is bottom centre.

Delete all the text from the Tooltip of the ‘line’ marks card.

Now make the chart dual axis and synchronise the axis. You’ll notice the bars sit on top of the circles, and the difference text is not displaying under the circles.

To remedy this, first, right click on the right hand axis, and select move marks to back. The lines should now be behind the circles.

Then, on the lines marks card, edit the Label text and just add a single carriage return in the dialog box, so the text is shifted down.

Adding Age to be displayed at the top

The quickest way to do this is to double click in the space on the Columns shelf, next to the Age pill, and type in ‘Dummy’ or just ” if you prefer. This has the affect of adding another ‘dimension’ into the view and as a result the Age values immediately get moved to the top.

Now just remove all axis, all gridlines, all row & column dividers. Then uncheck show header against the Dummy pill and hide field labels for columns for the Age header (right click on the Age label in the view itself to get this option, not the pill). Then format the Age text to be a bit bigger and bolder, and also increase the Size of the lines mark type. And that’s your viz 🙂

Additional Note

If you had followed Ryan’s blog post, then you would still have needed to create separate calculated fields to store the ownership values for 2015 and 2019, but these would have had to been LOD fields eg

Ownership 2015

{FIXED [Age]:SUM(IF [Year]=2015 THEN [Ownership] END)}

and a similar one for Ownership 2019. The other 2 fields related to the difference would then need to reference these instead.

My published viz is here.

Happy vizzin’!

Donna

Let’s Build a KPI Dashboard

Donna ColesLeave a comment

Sean set this fun and very relevant challenge this week displaying key measures (KPIs) along with further details for a selected measure displayed in a horizontal layout rather than the more traditional long-form, using ranking. The ‘gotcha’ part of this challenge was ensuring the tooltips displayed the measures in the right format, ie $ for Sales and Profit, % for Profit Ratio and a standard number to 0 dp for # of Orders.

We’ll start by

Building the KPI sheet

The data source I connected to already had Profit Ratio included, but if yours doesn’t, you’ll need to create as

Profit Ratio

SUM([Profit])/SUM([Sales])

You’ll also need to create

# of Orders

COUNTD([Order ID])

On a new sheet, double-click Sales and then double-click Profit to add them both to a sheet, and then use Show Me to display the fields as a text table.

Move Measure Names from Rows to Columns, then add Profit Ratio and # of Orders into the Measure Values section under the marks card. Re-order the measures into the required order.

Modify the format of each pill in the Measure Values section to the relevant format (millions, thousands, %)

Add Measure Names to the Text shelf, and adjust the text to be aligned middle centre and resize the fonts.

Hide the header (uncheck Show Header against the Measure Names in the Columns, and remove row dividers. Set the Tooltip not to show.

Building the Ranked Chart

The value to display in this chart is dependent on the KPI measure clicked on. We’re going to use a parameter to help drive this behaviour, so start by creating

Measure to Display

string parameter defaulted to ‘Sales’ containing 4 options: Sales, Profit, Profit Ratio, # of Orders

Based on the parameter, we need to determine which value to display

Selected Value

CASE [Measure to Display]
WHEN ‘Sales’ THEN SUM([Sales])
WHEN ‘Profit’ THEN SUM([Profit])
WHEN ‘Profit Ratio’ THEN [Profit Ratio]
WHEN ‘# of Orders’ THEN [# of Orders]
END

On a new sheet, add Sub-Category to Rows and Selected Value to Text and sort by Sub-Category descending. Show the Measure to Display parameter on the sheet and play around with the different options to see how the viz changes. When you’re happy it’s working as expected, reset back to ‘Sales’.

We’ll need to display the rank value, so create

Rank Value

RANK_UNIQUE([Selected Value])

and format this to be a number with 0dp but pre-fixed by #

Add this into the table.

Now, to display this information, we need to distribute each Sub-Category across 5 rows and 4 columns which is based on the Rank Value. So let’s work out which row and column each entry should be in

Rows

IF [Rank Value]%5 = 0 THEN 5 ELSE [Rank Value]%5 END

If the rank is divisible by 5, then place in the 5th row, otherwise place in the row associated to the remainder when divided by 5.

Cols

IF [Rank Value] <=5 THEN 1
ELSEIF [Rank Value] <=10 THEN 2
ELSEIF [Rank Value] <=15 THEN 3
ELSE 4
END

Add these fields to the table, so you can see how they’re working.

The Rank Value, Rows and Cols fields are all table calculations, and should be set to explicitly compute using Sub-Category.

On a new sheet, add Sub-Category to Detail, Cols to Columns as a blue discrete pill and Rows to Rows as a blue discrete pill. Add Selected Value to Columns. Drag Sub-Category from Detail on to Label and adjust to align the labels left. Widen each row slightly, so the text is visible. Adjust the Colour of the bars to be a pale grey.

Change the Measure to Display parameter to ‘Profit’, and notice what happens to the bars & labels associated to those with a negative profit. They’re positioned where you’d expect, on the negative axis, but this doesn’t match Sean’s solution.

Sean has chosen to display the values as absolute values (although the tooltips display the negative values). This means we need

Abs Value to Display

ABS([Selected Value])

Drag this field and drop directly on top of the Selected Value pill on the Columns shelf – this will replace the measure being displayed.

Now that’s sorted, we can get the ranking added.

Double click in the space next to Abs Value to Display on Columns and type in MIN(0) to create an additional axis and create a 2nd marks card.

On the MIN(0) marks card, move Sub-Category to the Detail shelf and then add Rank Value to the Label shelf. Make sure the table calc is set to compute by Sub-Category as before.

Change the mark type of the MIN(0) card to Gantt Bar, set the size to the smallest possible, and colour opacity to 0. The change of the mark type, will have changed the position of the rank label to be aligned to the left of the mark.

Make the chart dual axis, synchronise the axis and change the mark type of the Abs Value to Display back to Bar. Remove Measure Names from both of the marks cards.

Hide both axis and the headers and remove grid lines and divider lines. Make the zero line on the columns a solid line, slightly thicker.

Formatting the Tooltips

The tooltips display the selected measure name and associated value, formatted appropriately. As we’re using a ‘generic’ field to display the value, we can’t format this field as we’d usually do. Instead, I chose to resolve this using dedicated fields to store each value based on the measure selected.

Tooltip: Sales

IF [Measure to Display]=’Sales’ THEN [Sales] END

format this to $ with 0dp.

Tooltip: Profit

IF [Measure to Display]=’Profit’ THEN [Profit] END

format this to $ with 0dp.

Tooltip: Profit Ratio

IF [Measure to Display]=’Profit Ratio’ THEN [Profit Ratio] END

format this to 5 with 2 dp

Tooltip: #Orders

IF [Measure to Display]=’# of Orders’ THEN [# of Orders] END

format this to a number with 0 dp.

Add all these fields to the Tooltip shelf on the Abs Value to Display marks card, and then amend the tooltip so all these fields are listed side by side with no spacing. As only one of the fields will only ever contain a value, the correctly formatted figure will display. The Tooltip will also need to reference the Measure to Display parameter to act as the ‘label’ for the value displayed.

Finally adjust the title of the sheet to also reference the Measure to Display parameter,

Adding the interactivity

Add the sheets to a dashboard, then add a parameter action

Select Measure

On select of the KPI sheet, update the Measure to Display parameter, passing through the Measure Names value. When the measure is unselected, revert the display back to ‘Sales’.

Bonus – Colour the bars

As an added extra to call out the fact the negative values were being displayed on the positive axis, my fellow #WOW participant, Rosario Gauna, chose to colour the bars.

Colour Bar

[Selected Value] < 0

Add this to the Colour shelf of the Abs Value to Display marks card and adjust colours according.

My published viz is here. Enjoy!

Happy vizzin’!

Donna