Tag: Custom Number Format

Can you build a Fancy Text Table?

Donna Coles2 Comments

Ann Jackson provided this week’s challenge, to deliver a text table using only Measure Names & Measure Values. I thought with Ann’s introduction that “This challenge should be straightforward for users of all levels” that this would be relatively straightforward, but I have to confess there were moments that I struggled with this. I knew the fundamentals that I’d need to complete this; that all the columns except the first were going to need to be numbers (ie measures), that I’d have to use custom formatting to display the number in the required format (a shape, a date, a word), and that I’d need to use the ‘legends per measure’ functionality to colour each column independently of each other. But determining the best/worst date to display proved to be a bit tricksy! I got there in the end, but there was a fair bit of trial and error.

Custom Formatting

I’m going to step through the build of this, as I think that’s probably the easiest way to describe this challenge. But before I do, one of the core fundamentals to this is knowing about how numbers can be custom formatted. By that I mean when you right-click on a measure -> default properties -> number format -> custom

This box allows you to type in, but you need to know what format/syntax to use. If you set the formatting via one of the other options, then look at the Custom option, it’ll have an entry that will give you a starting point. The above is the format for a number set to 1 decimal place, and shows that negative numbers will be prefixed by a minus sign (-). If we wanted to always show a plus sign in front of a positive number, we can edit this custom formatting to +#,##0.0;-#,##0.0.

The first entry to the left of the semi colon (;) indicates what’s applied to positive numbers. The next entry, to the right of the semi colon, indicates what’s applied to a negative number.

With this knowledge, you can apply more ‘creative’ custom formatting to any numeric measure that contains positive and negative numbers. For example if you want to show a ☑ or a ☒ depending on a ‘yes/no’ or ‘true/false’ concept, then we can create a version of the field as a number along the lines of

Field as Number

If [Field] = ‘XXXX’ THEN 1 ELSE -1 END.

We can than custom format this field by entering ☑;☒ into the text box

The field can still be treated as a measure, since the underlying value is still a number (in this case +/- 1), it’s just displayed differently.

Building the measures

So now we’ve covered how this ‘sneaky formatting’ is working, we’ll get on with the overall build.

The data just needs data from 2019 & 2020, so I chose to set a data source filter to restrict to just these two years.

But, I wanted the rest of the challenge to derive the current year instead of hardcoding, so I created fields

Current Year

YEAR({MAX([Order Date])})

Last Year

[Current Year] – 1

From these, I could then use LoDs to create

CY SALES

IF YEAR([Order Date]) = [Current Year] THEN [Sales] END

This is formatted to $ with 0 dp

and then

LY SALES

IF YEAR([Order Date]) = [Last Year] THEN [Sales] END

again formatted to $ with 0 dp.

We then need an indicator which is ‘true’ if CY SALES is greater than LY SALES, but as discussed above, we need this to be a ‘measure’, which we can custom format.

CY vs LY

IF SUM([CY SALES])>SUM([LY SALES]) THEN 1 ELSE -1 END

Custom format this as ✅;❌ (just copy these symbols from this page… they’ll look black and white in the dialog) – check out this page, to lift the images/other symbols from.

The actual difference identified by △ (again just copy and paste this symbol into the field name) is simply

△

SUM([CY SALES]) – SUM([LY SALES])

formatted to $ with 0 dp. Once you’ve done this using the Currency(Custom) option, then go to the Custom option and add + to the front of the string :

+”$”#,##0;-“$”#,##0

Next up is the percentage difference

% DIFF
[△]/SUM([LY SALES])

again format this first to a Percentage at 1 dp, then edit the Custom format to +0.0%;-0.0%

Now we’re getting to the slightly more complex part of the challenge – to identify the best and worst day in the month. We’ll start with the best day. We’re using FIXED LoDs throughout this, and while it’s probably possible to do in a single calculation, we’ll use multiple calcs to build up the components.

Order Month

DATENAME(‘month’,[Order Date])

This is the one dimension that’s going to be used in the final output, and simply outputs the month name (January, February etc).

In the data set, there can be multiple sales (ie orders) in a single day. We want to identify the total sales in 2020 (ie the current year) for each order date.

Sales Per Day

{FIXED [Order Date] : SUM([CY SALES])}

Now we’ve got the total sales per day, we want to identify the value of the maximum daily sales in each month

Max CY Sales Per Month

{FIXED [Order Month]: MAX([Sales Per Day])}

Now we need to identify the date in the month that the max daily sales ocurred

BEST DAY

INT({FIXED [Order Month]: MAX(IF [Sales Per Day] = [Max CY Sales Per Month] THEN ([Order Date]) END)}) + 2

WOAH! WHAT??? Let’s try to break this down…

IF [Sales Per Day] = [Max CY Sales Per Month] THEN ([Order Date]) END

If the daily sales value is the maximum daily sales in the month, then return the associated Order Date. But we need to get a date per month, so we’ve wrapped this in a FIXED LoD, for each Order Month. LoDs require the value to be aggregated, so the IF statement gets wrapped in a MAX statement (note MIN would work just as well).

{FIXED [Order Month]: MAX(IF [Sales Per Day] = [Max CY Sales Per Month] THEN ([Order Date]) END)}

Finally, due to the nature of this challenge, that requires we only work with Measure Names & Measure Values, we will convert this date field to a number using the INT function.

The intention here, is that we can then use the Custom formatting option once again, to set the number as a date format – I chose dd mmm yyyy (ie 01 Jan 2020 format, as I feel its less confusing that working out whether the date is in UK or US format).

However, by a very weird circumstance, converting a date to an INT then formatting as a date, will give you a date 2 days out from the one you converted. I don’t understand why, and it left me scratching my head for some time. I had to sense check with a fellow #WOWer who had the same, and checking Ann’s solution, she also was handling the oddity, which is the reason for the +2 on the calculation.

We just create similar fields for identifying the worst day

Min CY Sales Per Month

{FIXED [Order Month]: MIN([Sales Per Day])}

WORST DAY

INT({FIXED [Order Month]: MAX(IF [Sales Per Day] = [Min CY Sales Per Month] THEN ([Order Date]) END)}) + 2

format this to dd mmm yyyy

The final measure we need is based on determining the rank of the CY SALES per month. Ie if we ordered the months based on CY SALES descending, the top 6 would be marked as ‘Top’ and the rest as ‘Bottom’.

RANK

IF RANK(SUM([CY SALES])) <= 6 THEN 1 ELSE -1 END

We can then custom format this to “Top”;”Bottom”

Formatting the Table

Create a text table by

  • Order Month on Rows
  • Measure Names on Columns
  • Measure Values on Text
  • Measure Names on Filter, filtered to just the relevant measures

Add Measure Values to the Colour shelf, and select the Use Separate Legends option to display multiple diverging colour legend controls.

It’s now a case of going through each measure and editing the colour palette, and other settings. Some of this was again a bit of trial and error for me – I chose options that worked.

For the black text fields (CY SALES, LY SALES, %DIFF), choose a diverging colour palette, then click on the coloured squares at each end and select black from the colour picker. Select Stepped Colour and reduce the steps to 2.

Apply the same concept to the BEST DAY and WORST DAY legends, but select the appropriate green or red colour instead.

For the remaining fields, select a diverging colour palette, select the appropriate red at one end, and green at the other, reduce the steps to 2

And subject to some other formatting tweaks (increase font sizes, centre text), this is enough to complete the challenge. My final published viz is here. Note, the published viz does have slight differences to what I’ve blogged… as with many things, you sometimes realise things can be simpler when you try again.

Happy vizzin’! Stay Safe!

Donna

What happens if? Can you update sales forecast and targets using only parameters?

Donna Coles6 Comments

So after Ann’s gentle workout for week 6, newly crowed Tableau Zen Master Lorna, hit us with this challenge, and I confess, I struggled. The thought of then having to write this blog about it even brought a little tear to my eye 😦

But here I am, and I will do my best, but I can’t promise I understood everything that went on in this. I truly am amazed at times how some people manage to be so creative and bend Tableau to their will. It really is like #TableauBlackMagic at times!

So I read the challenge through multiple times, played around with Lorna’s published viz, stared at the screen blankly for some time…. I found the University Planning Dashboard viz by Ryan Lowers that Lorna had referenced in the challenge as her inspiration (she’d linked to it from her published viz). I played around with that a bit, although that took a while for me to get my head round too.

I also did a google search and came across Jonathan Drummey‘s blog post : Parameter Actions: Using a parameter as a data source. This provided a workbook and some step by step instructions, so I used this as my starting point. I downloaded the workbook, copied across the fields he suggested and tried to apply his instructions to Lorna’s challenge. But after a couple of hours, it felt as if I was making little progress. I couldn’t figure out whether I needed 2 or 4 parameters to store the ‘list’ data source variables (one each to store the list of selected categories for forecast, the list of selected categories for target, the list of selected forecast values, and the list of selected target values, or one each to store the list of selected categories and forecast values combined, and selected categories and target values combined). Suffice to say I tried all combos, using a dashboard to show me what was being populated on click into all the various fields/parameters I’d built. But it just wasn’t giving me exactly what I needed.

I downloaded the University Planning Dashboard and tried to understand what that was doing. And finally I shrugged my shoulders, and admitted defeat and cracked open Lorna’s solution. When I finally get to this point in a challenge, I try just to ‘have a peak’, and not simply follow verbatim what’s in the solution. I gleaned that I did need only 2 parameters, and that what I had been doing with my attempts with Jonathan’s example was pretty close. It made me feel a bit better with myself.

How things then transpired after that I can’t really recall – it was still a lot of trial and error but I finally got something that gave me the Sales Forecast data and associated select & reset functionality (by this time I’d probably spent 4 hours or so on this over a couple of evenings). Once I’d cracked that, the target was relatively straight forward, so by the time I’d finished on the 2nd day, I had a dashboard that allowed the selections/resets and simply presented the data in a table on screen. I chose to keep that version as part of my published solution, just for future reference (see here). I then finished off the next day, building the main viz.

What follows now, is just an account of the fields etc I used to build my solution. So let’s get going….

Building the Sales Forecast Selector

I’m going to start by focusing on building the left hand side of the viz, setting and resetting the Sales Forecast values for each Category.

We need 2 main parameters to start with:

Forecast Param

An integer parameter defaulted to 70,000. This is the parameter that stores the value of the forecast to set.

Forecast List

A string parameter defaulted to empty. This is the parameter which will ‘build up’ on selection of a category, to store a delimited list of category + forecast values – ie the data source parameter.

Oh, and I also used a 3rd parameter, Delimiter, which is just a string parameter storing a :

The delimiter needs to be a distinct character that mustn’t exist in the fields being used. The Category field nor the Forecast Param field will contain a ‘:’, so that’s fine. But any other unused character would work just as well. Having this field as a parameter isn’t ultimately necessary, but it makes it easy to change the delimiter to use, if the chosen value doesn’t end up being suitable. It was also a field used in Jonathan Drummey’s solution I’d based my initial attempts on.

Now we need to build the viz to work as the category selector.

I simply put Category on the Rows shelf, sorting the pill by SUM(Sales) descending and set the Mark Type to circle. Oh – and I set a Data Source Filter to set the Order Date just to the year 2019.

I also needed the following

  • something to colour the circles based on whether the Category was selected or not
  • something to use to help ‘build up’ the List parameter ‘data source’
  • something to return the forecast value that had been selected against the specific Category

Category Exists in Forecast List

CONTAINS([Forecast List], [Category])

If the Category exists within the Forecast List string of text, this field will return true, and indicates the Category has been ‘selected’. This field is added to the Colour shelf, and the colour needs to be adjusted once parameter action has been applied to distinguish between true & false.

Add to Forecast List

if [Forecast Param]<>0 THEN
[Forecast List] +
[Category] + ‘_’ + STR([Forecast Param]) + [Delimiter]
ELSE ”
END

If the entered Forecast value isn’t 0, then append <Category>_<Forecast Value>: to the Forecast List parameter. Eg if the Sales Forecast value is $50,000 and Technology is selected, then Technology_50000: is added to the existing Forecast List parameter, which has started as blank.

If the Sales Forecast value is then changed to $10,000 say, and Office Supplies is selected, then the Forecast List parameter will become

Technology_50000:Office Supplies_10000:

This Append To Forecast List calculated field is used in conjunction with the Forecast List parameter within a Parameter Action on the dashboard to make all the ‘magic’ happen. The Append To Forecast List field must be in the view to be available to the parameter action, so it is added to the Detail shelf.

When a circle is selected the Append To Forecast List field is used to ‘set’ the Forecast List parameter, subsequently building up a string of Category_Value pairs.

Finally, on hover, the Category and the value of the selected sales forecast at the time must be visible on the Tooltip. To get the value at the point of selection, which isn’t necessarily the latest value visible in the Sales Forecast parameter displayed on screen, the following field is required:

Current FC Value

INT(if contains([Forecast List],[Category]) then
REGEXP_EXTRACT([Forecast List],[Category]+”_(-?\d+)”)
end)

This manages to pull out the number associated with the Category, so in the above example, would return 50000 for Technology and 10000 for Office Supplies.

This field has custom formatting applied : ▲”$”#,##0;▼”$”#,##0 and is added to the Tooltip shelf.

RegEx is a concept I have yet to really crack, so there is no way I’d have come up with the above on my own. I think it’s looking for the named Category followed by Underscore (_) followed by either 1 or no negative sign (-) followed by some numbers, and returns just the numeric part.

Finally, the circles shouldn’t be ‘highlighted’ when selected on the dashboard. To stop this from happening a calculated field of True containing the value True, and a field False containing the value False are required. These are both added to the Detail shelf, and a Filter Action is then required on the dashboard setting True = False. This is a technique that is now becoming a familiar one to use, having been used in earlier #WOW2020 challenges.

So my ‘selection’ sheet looks like

and when added to the dashboard, the parameter action looks like :

with the filter action looking like :

At this point, I’d suggest using a ‘test’ dashboard which contains the selection sheet, displays the Forecast List and Forecast Param, and has the dashboard actions described above, applied to get an idea of what’s going on when a circle is selected, and the values of the Forecast Param changed.

The final part to this set up, is the ‘reset’ button, which when clicked on, empties the Forecast List parameter.

Create a new sheet, change the Mark Type to Text, and on the Text shelf add the string ‘↺’. I simply typed this ‘into’ a pill, but you could create a calculated field to store the ‘image’, which isn’t actually an image, but a special string character, that I got off my favourite ‘go to’ unicode characters website.

You then need a calculated field

Forecast List Reset

”

that just contains an empty string. This is added to the Detail shelf.

Put this sheet on the ‘test’ dashboard, and create another parameter action

This takes the value out of the Forecast List Reset field and sets the Forecast List parameter, subsequently resetting the list to an empty string on click.

Verify this is all working as expected.

Building the Sales Target Selector

Subject to Sales Forecast selector working as expected, then apply exactly the same principles to create the Target selection sheet and associated parameters.

The only slight difference with the fields used in the Target selection is:

Add to Target List

if [Target Param]>0 THEN
[Target List] +
[Category] + ‘_’ + STR([Target Param]) + [Delimiter]
ELSE ”
END

This just applies the addition to the list if the entered target is a +ve number (ie > 0), rather than not 0 as in the forecast selection.

The Target also needs to be displayed on the Tooltip, and this time there is a default target value that should be displayed, even when no selection has been made. For this I created

Target

IF ZN(MAX([Current Target Value])) = 0 THEN
MIN(IF [Category]= ‘Furniture’ THEN 270000
ELSEIF [Category]= ‘Office Supplies’ THEN 260000
ELSEIF [Category]= ‘Technology’ THEN 250000
END)
ELSE MAX([Current Target Value]) END

which was formatted to a currency of 0 decimal places, prefixed by $. This was added to the Tooltip shelf.

At this point, you should now have both the ‘selection sheets’ working on the dashboard, so we can now focus on building the main viz.

Building the Bar Chart

Rather than building the bar chart, I first decided to build a tabular view that simply presented on screen all the bits of data I needed for the bar chart, this being

  • Sales value per Category (simply SUM(Sales))
  • Sales Forecast value per Category (ie Sales + selected Forecast value)
  • Selected Sales Target value per Category (this is the Target field described above)
  • % Difference between Sales & Target
  • % Difference between Sales Forecast & Target

So I created the following additional calculated fields:

Forecast

SUM([Sales]) + MAX([Current FC Value])

formatted to currency prefixed with $ set to 0 dp.

Forecast vs Target Diff

([Forecast]-[Target])/[Target]

custom formatted to ▲0%; ▼0%

Sales vs Target Diff

(SUM([Sales])-[Target])/[Target]

also custom formatted to ▲0%; ▼0%

Adding the table to the ‘test’ dashboard allows you to sense check everything is behaving as expected

Now its just a case of shifting the various pills around to get the desired view. Ensure at least one Sales Forecast Category has been selected, to make it easier to ‘see’ what you’re building.

Lorna stated the target should be displayed as a Gantt mark type, with the sales and the forecast displayed as bars. This means a dual axis chart is required, with sales & forecast on one axis and target on the other.

To get Sales and Forecast onto the same axis, we need to add Category to the Rows (sorted by Sales desc) and Measure Values to the Columns, filtering to only the two measures we need.

Set the Mark Type to bar, and add Measure Names to both the Colour and the Size shelf.

Adjust colours and sizes to suit.

You might have something like


where the measures are ‘stacked’, so the bar is the length of the Sales then the length of the Forecast. We don’t want this, so need to set Stack Marks to Off (Analysis menu -> Stack Marks -> Off).

Add all the necessary fields to the Label shelf and format accordingly (you may need to widen the rows to make the labels show against each row).

Note – in my solution I created some fields to make the opening & closing bracket around the Forecast v Target Diff value only show when a Forecast had been selected, however in writing this blog, I realise it was simpler just to change the formatting of the Forecast v Target Diff to add the brackets around the number. The custom formatting was changed to : (▲0%); (▼0%)

Adjust the Tooltip to suit too.

Now add Target to Columns alongside Measure Values. Set to Dual Axis and Synchronise the axis. Reset the Measure Values mark type back to bar if needed, and set the Target mark type to Gantt.

Remove Measure Names from the Colour and Size shelf of the Target marks card. Untick Show Mark Labels too. Adjust the colour of the mark to suit, and you should pretty much be there now…

Tidy up the final bits of formatting, removing/hiding the various axis, labels, gridlines etc etc.

When this is all put together on the dashboard, you might need to fiddle about a bit with layout containers to get the bar chart lined up with the Selector views.

And with that I’m done! My published version is here, along with the ‘check’ dashboard I used to sense check what was going on, as I’m sure if I ever looked at my solution again, I’d struggle to understand immediately 🙂

Once again, I just want to acknowledge those that manage to create this magic with Tableau. I applaud you!

Happy vizzin’!

Donna

Which months have the higher number of orders?

Donna ColesLeave a comment

For week 29 of #WorkoutWednesday2019, Luke Stanke set the challenge above (described here), which is comparing the overall average of orders placed per day against the average for each month.

On the face of it, this didn’t seem too bad (especially for a challenge set by Luke).  Some days when I tackle these challenges, the path I take can be long and arduous with several false starts along the way; the result being more of a ‘happy accident’ than anything of real coherence. Luckily for me, since I’d promised to start blogging on these challenges, this wasn’t one of them.

My usual approach to any of these challenges is to take some time reviewing the published viz on Tableau Public; hovering over the various marks to understand what’s on the tooltips and seeing if I can get any clues into how the various objects (marks, views, legends, titles etc) have been rendered, whether axis are being sneakily used, understanding the interactivity at play etc.

My next step is to then get the figures right based on what’s presented.  I typically like to create a ‘data’ sheet in the workbook – a tabular view of the data and associated calculations I have built, so I can easily sense check with the published viz whether my assumptions and computations are valid, and it provides a useful reference point if I’m trying to figure out what I did sometime later. 

So I started this challenge the same way.

First up, I assumed the ‘line’ was probably a reference line, and hovering over the viz confirmed this.  Everything else seemed pretty straightforward, so onto the figures.

1. Average no of orders per day per segment

This is what the line represents, and do to this I need to find the total number of orders placed per segment and the total number of days on which orders were placed for each segment.  The average is then just count orders / count days.

Some people will create everything in a single calculated field, but I like to break things up to help me troubleshoot if things don’t quite work as intended, so I ended up with 3 calculated fields, and since the overall average was required at a level higher than the level of detail being displayed (which is month in this viz), I figured LoD calculations were the way to go.

Count Orders Per Segment

{FIXED [Segment]: COUNTD([Order ID])}

For each segment, count the number of distinct orders that exist.

Count Days Per Segment

{FIXED [Segment]: COUNTD([Order Date])}

For each segment, count the number of distinct days on which an order was placed.

Overall Avg Orders Per Day Per Segment

SUM([Count Orders per Segment]) / SUM([Count Days Per Segment])

Format this to 2dp.

2. Avg no of orders per day per segment per month

This is what each coloured ‘bar’ represents, and for this I needed to find the total number of orders placed per segment per month, and the total number of days in each month on which orders were placed for each segment.   I chose to stick with LoDs again for this :

Count Orders Per Segment Per Month

{FIXED [Segment], MONTH([Order Date]): COUNTD([Order ID])}

For each segment and month, count the number of distinct orders that exist.

Count Days Per Segment Per Month

{FIXED [Segment], MONTH([Order Date]): COUNTD([Order Date])}

For each segment and month, count the number of distinct days that an order was placed.

Avg Orders Per Day Per Segment Per Month

SUM([Count Orders Per Segment Per Month]) / SUM([Count Days Per Segment Per Month])

Format this to 2dp.

Putting these fields out in a table, you can see the first three columns contain the same values for each segment even though the data is being displayed at the month level.  The final 3 columns are the monthly figures.  These numbers all reconcile back to the data displayed on the viz.

So I’m heading in the right direction, now onto the next bit.

The requirement is to “Label the bars with the percent difference between the monthly value and the overall value”, so I need another field…

3.  % Difference

…which is basically the difference between column 3 and column 6 above, as a proportion of column 3

([Avg Orders Per Day Per Segment Per Month]-[Overall Avg Orders Per Day Per Segment])/[Overall Avg Orders Per Day Per Segment]

By default though, this shows 0s when added to the table as the numbers calculated are actually 0.38, -0.33 etc, so it needs formatting as a percentage to 0 decimal places.  However, applying the standard ‘percentage’ number format won’t quite cut it for this challenge, as Luke has labelled the positive numbers with a + too.  To get this, I need to apply custom formatting.

The easiest way to get this right I find, is to use one of the default number formatting options to set the number in whatever ‘main’ format you need, eg if you have a monetary value to display in £k, use the Currency (Custom) to get all the settings right.  In this instance though I want Percentage set to 0 dp..

Once done, press ok to close the dialog box, then go to set the number format again, but this time choose Custom.  The formatting ‘style’ applied previously will be shown

and it can then be modified to get the desired format, in this instance I change to

It’s often these little formatting tips that get thrown into the #WorkoutWednesday challenges that I love the most, although sometimes they can be tucked away and hard to find (or remember).

So at this point I now think I can start to build the viz, so adding the various pills I need, and adding a reference line I get this…

A line chart isn’t what I want though, and it’s not bars either, as they start from 0.  The mark type I need is gantt

which makes me realise I need to create another field…

4.  Difference

The gantt chart has the marks in the right position, but to make the ‘bars’ I need to alter the size, and that size is the difference between the mark position and reference line, which is

([Avg Orders Per Day Per Segment Per Month]-[Overall Avg Orders Per Day Per Segment])

Placing this field on size, gives me this but while the bars are the right height, they’re not in the right place.

Using gantt bars in this way is akin to the technique used in building waterfall charts, and is rectified simply by applying a multiple of -1 to the pill on the size shelf (in this instance [Difference]).

As a shortcut, I simply type this into the pill on the size shelf itself, which is a nifty little trick.

And voila! The viz is essentially there now.  Just need to add colour and further formatting …

The ‘bars’ are coloured based on whether they’re above or below the line, ie whether the difference is positive or negative, so another field is needed :

5. COLOUR : Difference

IF [Difference]>=0 THEN ‘green’ ELSE ‘blue’ END

And popping this on the colour shelf, and adjusting the colours to suit, gives me

The main thing left now is the little formatting bits and pieces :

Borders : remove columns

Axis : remove title, set to be independent, and set not to start at zero

Reference Line : change the label font size, align left middle, and set the shading to have a white background and 100% opacity

Reference Line TootlTip (new feature in v2019.2): set to Custom as below

Note if I’d simply called my field ‘Average’ rather than ‘Overall Avg Orders Per Day Per Segment’, I wouldn’t have needed this step, though it’s always useful to try out the new ‘little’ features if you can 🙂

Label : add [% Difference] to label shelf, and format centre middle

Tooltip : add relevant fields and adjust formatting as appropriate

The final thing I noticed was the axis scale – my scale was 2dp due to the formatting of my [Avg Orders Per Segment Per Month] field.  Luke’s axis was mixed – some scales at 1dp and some with 0.

I tried a few things, like formatting the axis to be Number (Standard) which has the effect of ‘automatically’ showing a number as a decimal or a whole numbers (something I recall from a very early WorkoutWednesday challenge a couple of years back).  But this didn’t give me the desired effect.  I ended up setting the axis format to be 1 dp.  But this then meant the value on the tooltip also ended up displaying as 1dp, when I wanted it to be 2 *sigh*

To fix this I created a duplicate field of the measure being displayed (a copy of [Avg Orders Per Segment Per Month]), and formatted it to 2dp, and placed this on the tooltip instead. 

I’m going to have to have a peak at Luke’s solution to figure out what magic he’s done here….

Very final step was to add to a dashboard, and add the title and my own custom footer.

So phew done!  My published version is here.

If you’ve got this far, thank you for reading J  I can’t guarantee all write ups will be to this level – it’ll partly depend on the challenge itself, and what path I head down to solve it.

I’m now off to have a peak at Luke’s challenge to figure out that pesky axis……. or I would if his workbook was downloadable 😦

Happy vizzin!

Donna