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.
Ann Jackson returned this week with a challenge primarily focussed on formatting.
The core requirement this week was to be able to present different measures on a chart, based on a user selection, but where the values displayed were of differing numerical formats
Sales per order in $ to 0 decimal places, formatted to show a ‘,’ every 1,000.
Profit Ratio as a % to 1 decimal place
Items per order as a numerical value to 2 decimal places
My focus points this week are
Measure swapping
Adding the line labels
Labelling the y-axis
Adding the reference lines
Building the blocks
Measure Swapping
This is technique that should be in everyone’s arsenal, as it’s a great way to present multiple views of the data without the need for multiple instances of the chart – it saves space and clutter but continues to allow flexibility.
The 3 measures required needed to be defined through calculated fields
Profit Ratio
SUM([Profit])/SUM([Sales])
Sales per Order
SUM([Sales])/COUNTD([Order ID])
Items per Order
SUM([Quantity])/COUNTD([Order ID])
A parameter is also required to allow the user selection. I chose to use a string parameter with the various measures displayed as below
SELECT A MEASURE
Then to pull this altogether, I needed to build a calculated field to store the relevant value based on the parameter value selected
Display Measure
CASE [SELECT A MEASURE] WHEN ‘Profit Ratio’ THEN ROUND([Profit Ratio]*100,1) WHEN ‘Sales Per Order’ THEN ROUND([Sales Per Order],0) WHEN ‘Items Per Order’ THEN ROUND([Items Per Order],2) END
It’s within this field I chose to define the number formatting I wanted to display, and by then setting the number format of the field to Number Standard, it seemed to show what I intended on hover. Add Display Measure to Rows and plot against QUARTER(Order Date) coloured by Category to get the display below.
Adding the line labels
However while the numeric format is what’s required, I haven’t got the $ or % symbol, and I can’t apply that as part of the default formatting.
Instead I created explicit prefix & suffix fields
$ Label Prefix
IF [SELECT A MEASURE] = ‘Sales Per Order’ THEN ‘$’ END
% Label Suffix
IF [SELECT A MEASURE] = ‘Profit Ratio’ THEN ‘%’ END
Adding these 2 fields to the Detail shelf, they can then be referenced in both the Tooltip and the Label as follows
Based on the logic, either both fields will be NULL/blank else, only one will be populated, so you’ll never get $1,000% displayed!
Labelling the y-axis
Amend the y-axis to delete the Display Measure title, then add the SELECT A MEASURE parameter to the Rows shelf. Rotate and format accordingly.
Adding the Reference Lines
Adding a reference line – simples, surely! But why would Ann be making a challenge if it was that easy….hmmmmm! So what were the challenges posed here
If you add an ‘Average’ reference line, you don’t get a value per line (even if you select the ‘per cell’ option) – you just get one average line. If the chart was split so there was a row per category, you’d be able to get this.
2.The lines displayed can’t be created via a ‘dual’ axis chart where the 2nd axis is showing the average, because the line format is a finely dotted line, and we can’t format a line mark this way. Proper reference lines can be formatted though, so I concluded the lines had to be true reference lines.
3. However, the labelling of a reference line is quite limited, and while I can show the value, I can’t use other calculated fields (ie the Prefix/Suffix fields) on the reference line label…
I came up with the following solution : create separate fields to store the AVG values for each Category, so that I could add 3 separate reference lines to the main chart; then create a dual axis line chart which also showed the average per category, label the line accordingly, and reduce the opacity of the line to 0%.
Ref Line Per Category
WINDOW_AVG([Display Measure])
Stores the average of the data displayed, and can be varied based on the table calc settings.
Ref Line – Tech
IF MIN([Category]) = ‘Technology’ THEN [Ref Line per Category] END
Only stores the average for the Technology data. I created equivalent ones of these for Ref Line – Office and Ref Line – Furniture
All 3 fields were added to the Detail shelf, then added as 3 different reference lines, coloured and formatted as a dotted line accordingly
To make the labels, I added Ref Line per Category to the Rows shelf to create a secondary axis. The table calculation was set as below
This produces a straight line for each category on a second chart, which I duly labelled by choosing to label the start of line, and aligning top left
I then set the opacity of the line colour to 0%, which makes the line disappear
I then set the chart to be dual axis, and synchronised the axis.
Building the blocks
For the block chart, I started by building a Tree Map (using Show Me) based on Category and the Ref Line Per Category fields.
I created a Rank field as
RANK([Ref Line per Category])
which I added to the Tooltip. I also then filtered the chart to Rank=1 to give me the main block. I then duplicated this sheet, and changed the rank filter to Rank=2, and repeated again for Rank=3. This gave me 3 sheets I could then organise onto the dashboard, as just using the tree map view directly, I couldn’t control how the different sections would display.
This was a fun challenge this week, slightly less taking than the previous weeks! My published viz is here.
This week’s #WOW challenge was set by Ann primarily to demonstrate the recent 2020.2 release of relationships in Tableau. This is a change to how the data modelling works when combining data together, in this case actual sales data and sales goal (target) data. Ann provided a ‘tweaked’ version of superstore sales data for the actual sales, alongside a custom ‘goals’ data set which provided a sales goal value for each month per Segment and Category (ie the goals data was not as granular as the actual sales data).
Combining the Data
Obviously, to make use of the new relationships feature, you’ll need at least v2020.2.1 installed.
When you add the 2 data sources to the Data Source pane and add the Orders sheet from the Superstore data and then add the Sheet 1 from the Sales Goals data, you’re prompted to add a relationship if matches can’t automatically be found (ie the field names aren’t identical).
I added relationships for the Segment, Category and Month of Order Date fields, which then provides a ‘noodle’ link between the two sets of data
Note – to add multiple relationships, you have to close the field listing to then see the Add more fields option.
With relationships, Tableau is now smarter at how it manages the queries between the data. I’m not going to go into it at length (as there’s plenty of good Tableau documentation on the subject), but I’ll quickly show the difference between this and a traditional ‘join’.
In the Relationships model, if I show Sales and Goal by Month & Segment, the Goal is based on the sum of the rows associated to that Month and Segment that you can find if you looked at the source spreadsheet directly
But if I was using a traditional join with these data sets :
and then presented the same info as above
The Goal data is way off, as it’s being influenced by all the additional rows of data that have been constructed by the join clause.
If we expand to the Category level in each example above (the level at which the Goal data is stored), the Relationship version still shows the correct value
while the Join version is still incorrect, and only changing to an Average aggregation do we get the figure needed
Joining the data requires more work (calculations / knowledge) to ensure the Goal data is reporting correctly at the various levels, whereas the Relationships model ‘just works’.
Building the Table
When I started building the solution, I actually started with the YoY trend chart, but found I had to revisit it (swap a couple of pills about) as I progressed through, so I’m going to start with the table of data itself.
This turned out to be really quite tricksy… it’s just a table right… looks harmful enough…. but Ann had lobbed a few ‘gotcha’ grenades into the mix, that took a bit of head-scratching!
First up, Ann likes to use capitalisation, so I simply renamed the following fields
Category (from Sheet 1 – the Goals data) -> CATEGORY
Segment (from Sheet 1 – the Goals data) -> SEGMENT
Month of Order Date (from Sheet 1 – the Goals data) -> MONTH
Sub-Category (from Orders – the Superstore Sales data) -> SUB-CATEGORY
And I then created a hierarchy called SEGMENT which stored SEGMENT – > CATEGORY -> SUB-CATEGORY.
We also needed to ‘fake’ today so we can show sales to date. The Orders Superstore data set contains data from the start of 2016 to the end of 2019. We needed to pretend ‘today’ was 1st July 2019 and not show any sales beyond the end of the month. So I created the field
Today
#2019-07-01#
and then
ACTUAL SALES
IF DATETRUNC(‘month’,[Order Date])<= [Today] THEN [Sales] END
If we build out the basics of the table
SEGMENT hierarchy on Rows
MONTH (discrete, exact date, formatted to mmmm yyyy) on Rows
Measure Names on Columns and on Filter, filtered to ACTUAL SALES and Goal
Measure Values on Text
MONTH on Filter, set to exclude 2016
we get
and as we expand the hierarchy to the next level, the ACTUAL SALES and Goal adjusts as expected
but when we reach the 3rd level we see a couple of issues
The Goal is showing the values that were displayed at the CATEGORY level, since it isn’t set at any further level. The requirement we have is to show the value ‘No Goal’ instead once we reach this point.
We also have a set of values with a NULL SUB-CATEGORY. This is because there are dates with goal values in the Goals data set, but we have no Sales data for those dates, so a match at the SUB-CATEGORY level can’t be found. We need to remove these records when at this level, and actually only show records where we have ACTUAL SALES, so records from July 2019 onwards should also be excluded.
At this point, I did end up having a sneak look at Ann’s solution. I had an idea of what I wanted to do, BUT I was worried that I may be missing something related to the new data model… that perhaps there was a feature of relationships that I needed to be exploiting as part of this, rather than go down the table calculation route I was heading towards. After all, part of doing these challenges for me is to make use of new features & functions if I can, so I have a reference workbook for future use. I had a read up on the blog posts related to relationships, but couldn’t spot anything obvious, hence a quick check at Ann’s solution…. nope nothing special required in respect of the data model….so instead, we need some additional calculations to help resolve all this.
What we need is some way to identify when we’re at the lowest SUB-CATEGORY level. We’ll start by counting the number of distinct sub-categories we have
# Sub Cats
ZN(COUNTD([SUB-CATEGORY]))
Wrapped in a ZN will return the value 0 when none exist.
Dropping this into the table of data, we can see the 0s for all the future Goal records, and 1 for the rest of the rows, since we’ve expanded to the SUB-CATEGORY level.
If you collapse up to the CATEGORY level, the #Sub Cats count changes, as each row typically contains more than 1 SUB-CATEGORY.
The term ‘typically’ in the above statement though is the reason we can’t assume we’re at the lowest level if the count is 1 though. It is possible for the sales against a particular CATEGORY in a MONTH to only span a single SUB-CATEGORY (Check out Home Office -> Furniture in Jan 2018). So we also need
Max Sub-Cats in Window
WINDOW_MAX([# Sub-Cats])
Which will count the maximum number of SUB-CATEGORY records for each SEGMENT and CATEGORY. In the case of Furniture Sales in the Home Office in Jan 2018, you can see below the count of sub-categories is 1 but the maximum count in the window is 4.
This shows we’re not at the SUB-CATGEORY level, as when we are, the #Sub Cats = Max Sub Cats in Window (ie 1=1)
So with that knowledge we can now derive an alternative field for the goal
SALES GOAL
IF ([# Sub-Cats] = 1 AND [# Sub-Cats]=[Max Sub-Cats in Window]) THEN 0 ELSE SUM([Goal]) END
Remove the original Goal field from the table and add this instead, setting the table calculation to compute by MONTH and you’ll see the SALES GOAL value change from a value to 0 as you expand down to the SUB-CATEGORY level.
So how do we show ‘NO GOAL’ instead of 0? Well this is a bit of a sneaky formatting trick, and one I couldn’t figure out (this step was actually the last thing I changed before publishing).
In my original SALES GOAL calculation I had set the value to be NULL rather than 0 expecting there to be an option to show the NULL values as something else (ie NO GOAL). But the feature I was looking for wasn’t available (maybe I was mistaken about when it appears.. maybe it’s disappeared in 2020.2… I still need to investigate). Anyhow, my alternative was to create a string field to store the value in, but this would make formatting the number quite cumbersome, and I was sure that wasn’t the case.
So I had to check Ann’s solution. Instead of NULL, she had set the value to be 0, and consequently used the following custom number formatting :
“$”#,##0;-“$”#,##0;”NO GOAL”
where the last bit is how to format 0. Very very sneaky huh!
So we’ve worked out how to identify when we’re at the SUB-CATEGORY level, and managed to display ‘NO GOAL’ when at that level, now we need to filter out some of the rows (but only when at this level).
Records to Show
IF [Max Sub-Cats in Window] = 0 THEN FALSE ELSEIF ([# Sub-Cats] = 1 AND [# Sub-Cats]=[Max Sub-Cats in Window]) THEN //at lowest level IF ATTR([MONTH]) < [Today] THEN TRUE ELSE FALSE END ELSEIF [# Sub-Cats] = 0 AND [Max Sub-Cats in Window] = 1 THEN FALSE //still at lowest level but no sales so exclude ELSE TRUE END
This took a bit of trial & error to work out, and there may well be something much more simplified.
Adding this to the table, and again setting to compute by MONTH you can see which rows are reporting True and False. When collapsed to the SEGMENT or CATEGORY level, all rows are True, but the values start to vary when at the SUB-CATEGORY level.
Now, we need to work on the fields to help show us the actual & % difference.
RESULT
IF ([# Sub-Cats] = 1 AND [# Sub-Cats]=[Max Sub-Cats in Window]) THEN NULL ELSE SUM([ACTUAL SALES])-([SALES GOAL]) END
The difference should only show if we’re not at the lowest level. This needs to be custom formatted as
will give me the % difference (format to percentage with 0 dp).
And the final piece of data we need (I think) is to compute a RAG status for each row. This is dependent on some parameters
GREEN (WITHIN X%)
Type Float, set to 0.1 but displayed as a percentage
and similarly
RED (ABOVE Y%)
With these we can work out
RAG
IF ABS([ACTUAL vs GOAL]) > [RED (ABOVE Y%)] THEN ‘WAY OFF TRACK’ ELSEIF ABS([ACTUAL vs GOAL]) < [GREEN (WITHIN X%)] THEN ‘ON TRACK’ ELSEIF ATTR([MONTH])>=[Today] THEN ‘NO COMPARISON’ ELSEIF [SALES GOAL]=0 THEN ‘NO COMPARISON’ ELSE ‘OFF TRACK’ END
If you add all these fields to your table then you can check how they all compare/change as you expand/collapse the hierarchy (make sure you set the compute by MONTH on all fields).
So now we’ve got all the building blocks you can create the actual table.
I’d recommend duplicating the above (and keeping as a ‘check data’ sheet). Then remove the fields you don’t need, and add the Records To Keep = True to the Filter shelf. Format the table appropriately, and add the title so you’re left with
Building the Line Chart
As mentioned above, I started with this chart, but ended up having to revisit it as I found I needed to use different pills that got created as I built the table. There is actually 1 additional field needed, which isn’t obvious until you start playing with the dashboard actions later:
GOAL
IF [SALES GOAL] > 0 THEN [SALES GOAL] END
This is needed so that when we filter by SUB-CATEGORY later and there is no goal, we only get a single line displaying the sales values, rather than also having a line plotted at 0.
MONTH on Filter, excluding Year 2016
MONTH (exact date, continuous) on Columns
Measure Values on Rows and Filtered to ACTUAL SALES and GOAL
Coloured accordingly by Measure Names
Then add TODAY to Detail and add a Reference Line to the MONTH axis, setting it to TODAY, adjusting the format of the line, and setting the fill above colour to grey.
Format the gridlines, tooltips & axis accordingly to match
Barcode / Strip Chart
For this we’re creating a bar chart using MIN(1), and fixing the axis to range from 0-1 so it fills up the entire space as follows
MONTH on Filter excluding Year 2016
MONTH as exact date, discrete (blue pill) on Columns
MIN(1) on Rows
RAG on Colour adjusted accordingly
ACTUAL vs GOAL on Tooltip
Then use the ‘type in’ functionality to create a pill labelled ‘vs Goal’ on the Rows
Creating the Dashboard
I used a vertical container to add the components onto the dashboard, as I used blank objects that are formatted with a black background, 0 padding and a height of 4 to create the horizontal line separators.
The interactivity between the table and the other charts is managed via Dashboard Filter actions.
To get these to work properly though, and in such a way that the selected SEGMENT and/or CATEGORY and/or SUB-CATEGORY can be referenced in the title of the line chart, 3 separate dashboard actions are required, with each one specifying the field to filter by, as follows :
Once these are set up, the associated pills will appear on the Filter shelf of the Line and Barcode charts, and are then available for selection when building the title of the line chart
Week 2 of #WOW2020 saw Ann post this challenge asking us to create a dynamic bar chart that changed both the measure being reported and the timeframe being reported over, based on user selections.
The measure needed to be displayed as the bar label, which needed to be formatted differently depending on whether it was Sales ($ 0 dp), Profit Ratio (% 1 dp) or Items Per Order (1 dp). Due to this, the label can’t be a single field, as it’s not yet possible in Tableau Desktop to apply number formatting within a calculated field *sigh*.
A couple of calculated fields are needed for the measures
Profit Ratio
SUM([Profit])/SUM([Sales])
and Items Per Order
SUM([Quantity])/COUNTD([Order ID])
The timeframe format displayed also needed to change based on whether the timeframe selected was Last 12 months (MMM’YY eg Apr’19), Last 13 Weeks (Week No) or Last 14 days (mm/dd eg 12/31).
The bar colour needed to reflect the measure selected, and measure selector needed to display a filled or open circle depending on whether the measure was selected or not.
Measure Selector
I realised there were a lot of similarities with the behaviour of the measure selector, and Luke’s challenge from the previous week. I’m not sure if this was intentional or not. As a consequence though, I’m not going to explain how I built the Measure Selector chart, as I utilised exactly the same principles for the Week 1 solution, and I’ve already blogged about it here.
Bar Chart
I built the bar chart on a single sheet, plotting a measure that could change based on selection, against a date field that also changed on selection.
To drive the selection change I needed 2 parameters:
Date Period
A string parameter containing the 3 values available
This parameter was just made available as a drop down for the user to select from on the dashboard.
Selected Measure
This was a string parameter that just stored a piece of text. It was set to the value ‘ Sales ‘ by default, but was changed via a Parameter Action following selection via the Measure Selector chart discussed above.
Note the leading & trailing space in the default value ‘ Sales ‘due to the alias that needs to be set – refer to my previous bog post to understand the relevance
The date field to plot then had to alter based on the Date Period parameter, so the following calculated field was created:
Order Date Truncate
DATE(CASE [Date Period] WHEN ‘Last 12 Months’ THEN DATETRUNC(‘month’,[Order Date]) WHEN ‘Last 13 Weeks’ THEN DATETRUNC(‘week’, [Order Date]) ELSE DATETRUNC(‘day’, [Order Date]) END )
This field is essentially storing the date as the 1st of the month, 1st day of the week or specific day level, so when plotted on a chart it can be set to ‘exact date’ and all the orders in the same month/week/day, are all grouped together.
The measure field to plot alters based on the Selected Measure parameter, so the following calculated field is required :
Measure to Show
CASE [Selected Measure] WHEN ‘ Sales ‘ THEN SUM([Sales]) WHEN ‘ Profit Ratio ‘ THEN [Profit Ratio] WHEN ‘ Items Per Order ‘ THEN [Items Per Order] END
Plotting Order Date Truncate on columns as a discrete exact date against Measure to Show on rows with mark type set to Bar, and you can use the parameter to change the number of bars.
But this is showing all the months/weeks/days, so we need to add a filter to restrict to amount of information showing.
Dates to Include
CASE [Date Period] WHEN ‘Last 12 Months’ THEN [Order Date] >= DATEADD(‘month’,-12,[Today]) WHEN ‘Last 13 Weeks’ THEN [Order Date] >= DATEADD(‘week’,-13,[Today]) ELSE [Order Date] >= DATEADD(‘day’,-14,[Today]) END
This will true or false, and is added to the Filter shelf set to True
Note – [Today] is another parameter that is defaulted to 01 Jan 2020 as per Ann’s requirement.
The field names for the Order Date Truncate isn’t displaying as required though. To fix this, I created
Label:Date
CASE [Date Period] WHEN ‘Last 12 Months’ THEN LEFT(DATENAME(‘month’, [Order Date Truncate]),3) + ”” + RIGHT(STR(YEAR([Order Date Truncate])),2) WHEN ‘Last 13 Weeks’ THEN ‘Week ‘ + STR(DATEPART(‘week’, [Order Date Truncate])) ELSE STR(DATEPART(‘month’,[Order Date Truncate])) + ‘/’ + STR(DATEPART(‘day’, [Order Date Truncate])) END
which is building the required string to display.
Add this to the Columns shelf next to the Order Date Truncate field, then untick Show Header against the Order Date Truncate field
Next up is the labels to display on top of each bar. This also needs to show something different depending on the measure selected. For this I built 3 calculated fields:
Label: Sales
IF [Selected Measure] = ‘ Sales ‘ THEN [Sales] END
which is formatted to $ with 0 decimal places
Label: Profit Ratio
IF [Selected Measure] = ‘ Profit Ratio ‘ THEN [Profit Ratio] END
which is formatted to % with 1 decimal place, and
Label: Items to Order
IF [Selected Measure] = ‘ Items Per Order ‘ THEN [Items Per Order] END
which is formatted to 1 decimal place
These 3 fields are then added to the Label shelf, and the label text is then edited to ensure all the fields are positioned side by side. Due to the fact only 1 of these fields will ever contain data, it looks as though there is only 1 label showing
Next we need to colour the bars, which is based on Selected Measure which is just added to the Colour shelf. When it’s added though, you only get a single measure in the colour legend at a time, so to set the colours you need to set Selected Measure to be each option of Sales, Profit Ratio, Items to Order (based on what the alias has been set though), either by manually typing into the parameter, or selecting a measure on the dashboard via the Measure Selector chart you built initially.
The tooltip is then set as follows
and once again the Label:xxx measures are all positioned alongside each other as, remember, only one will ever contain any data.
Finally it’s just a case of tidying up the chart – remove the rows & columns, the Measure to Show axis, and the Label:Date label.
The zero line of the rows is set to be a thicker grey line to get the desired display
Once done, the bar chart can be added to the dashboard, and if the Parameter Action on the Measure Selector chart has been set up properly, the bar chart display should change as that chart is interacted with.
Donna + DataViz 