Tag: Week 6

Can You Adjust the Dashboard Design by Filter?

Donna ColesLeave a comment

The focus of this week’s challenge, set by Yoshi, is showing “detail on demand” using filters.

Defining the calculations

We’re only concerned about current year and previous year, so to simplify this, after connecting to the data source, I added a data source filter based on Order Date to restrict the information to years 2023 and 2024 only.

I then created the following fields

Latest Year

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

Previous Year

[Latest Year]-1

Sales – LY

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

Format to $ with 0 dp

Sales – PY

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

Format to $ with 0 dp

YoY

(SUM([Sales – LY]) – SUM([Sales – PY])) / SUM([Sales – PY])

Custom format to +0%;-0%;0%

Building the Sales KPIs

On a new sheet, add Category to Columns, and Sales – LY and YoY to Text and format/layout the text as required. Adjust the formatting of the column headings too.

Add YoY to Colour too to match the solution (though personally, if I was doing this for a business need, I wouldn’t do this as some of the text becomes quite washed out).

Building the Sub Category Viz

On a a new sheet, add Category and Sales – LY to Columns and Sub-Category to Rows.

However, you can see that as not every Sub-Category exists in every Category, we get ‘blanks’ in the display, so we need use something on rows that exists for every Category.  We can do this using the rank of Sales.

Add Sales -LY to Rows and change it to be a discrete (blue) dimension.

Move Sub-Category to Detail and then apply a quick table calculation of rank to the Sales – LY pill on Rows. Adjust the table calculation so it is computing explicitly by Sub-Category only.

Make the rows a little wider, move Sub-Category to Label. Add Sales -LY to label too. Adjust the format/layout of label and align middle right. Add YoY to Colour.

Add Sales – PY to Detail then add a Reference Line to the Sales – LY axis, that refers to Sales – PY per cell.

Add Latest Year to Tooltip and adjust the text to suit. Finally, hide the Sales – LY Rank row header and the Category column header (uncheck show header) and remove the title of the Sales – LY axis. Add a title.

Building the Product Viz

On a new sheet, add Product Name to Rows and Sales – LY to Columns. Sort by Sales – LY descending. Hide the null value indicator.

Add YoY to Colour. Add Sales – LY to Label. Add Sales – PY to Detail and add a Reference Line as described above. Add Sub-Category to Detail and add Latest Year to Tooltip and adjust. Remove the title of the Sales – LY axis, and hide the Product Name column header title (right click and hide field labels for rows). Add a title.

Filtering the display

The requirement is to be able to ‘filter’ the display based on the Category selected, and in the event not all categories are selected, the Product bar chart should show. Due to this second requirement, just using a standard ‘quick filter’ is a bit tricky – we need a way to understand what has been selected in the filter.

We can use a Set for this. Create a set of categories – right click Category > create > set

Category Set

Select all values

Add this to the Filter shelf of the KPI sheet, and then apply this same filter to the bar chart sheets too (right click the pill on the Filter shelf > apply to worksheets > selected worksheets > select the relevant sheets).

If you select show set, this then displays the input control to select which options are in or out of the set.

To manage the visibility of the Product bar chart, we need to know how many Categories there are in total, and how many Categories are selected. So create

Count Categories

{FIXED: COUNTD([Category])}

And

Count Set Members

{ COUNTD(IIF([Category Set], [Category],NULL))}

Both of these are LODs as we need to reference them in another boolean calculation that will be used to drive dynamic zone visibility.

Not all Categories Selected

{(SUM([Count Categories]) <> SUM([Count Set Members]))}

Building the dashboard

Create a dashboard. The three sheets should be arranged within layout containers. I used a horizontal container, where the left hand column contained a vertical container which in turn contained the KPI sheet on the top and  the Sub Category bar chart underneath. The right hand column of the horizontal container contained the Product bar chart.

To control the visibility of the Product bar chart, select the object, then from the Layout tab on the left hand side, select the control visibility using value checkbox, and choose the Not All Categories Selected field.

The bar will initially disappear, but if you then deselect a value in the Category ‘filter’ control, it will reappear.

Added extra

We can also set the Product bar chart to filter if a Sub-Category bar is clicked on. Create a dashboard filter action

Filter Products

On select of the Sub-Category bar chart sheet, target the Product Bar Chart sheet passing all values. Show all values when the selection is cleared.

My published viz is here.

Happy vizzin’!

Donna

Top & Bottom Variance

Donna ColesLeave a comment

For #WOW2024 Week 6, Sean challenged us to create a viz depicting the top and bottom entries based on a variable set by the user. He instructed that no LoDs (level of detail calculations) should be used, and then also added a bonus to complete the challenge without using sets either (and so hinting that sets would solve the problem). I built both, and will provide the solution for both.

Setting up the common fields

Regardless of the solution, various calculated fields and parameters are required.

pTop

integer parameter defaulted to 12, which is used to drive how many entities to display in our top & bottom cohorts.

For the user to select a month, I first created a new field

Month of Order Date

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

and I changed the default sort order of this field to descending (right click the field > default properties > sort > choose descending in the disalog box).

I then created a parameter

pSelectedMonth

date field that selects from a list that is populated when the workbook opens from the Month of Order Date field. Default to July 2021 and format the display to a custom format of mmmm yyyy – having defined the sort order to be descending, the most recent month will be at the top (this wasn’t in the requirements, but is just a useful trick to know).

Now we have a handle on the month we want to report over, we can create

Selected Month Sales

ZN(IF [Month of Order Date] = [pSelectedMonth] THEN [Sales] END)

Return the sales only if the month is that selected, otherwise return 0 (this is what the ZN function will do)

Format this to $ with 0dp.

Previous Month Sales

ZN(IF [Month of Order Date] = DATEADD(‘month’, -1, [pSelectedMonth]) THEN [Sales] END)

Return the sales only if the month matches the previous month to that selected, otherwise return 0.

Format this to $ with 0dp.

and with these, we can then create

Difference in Sales

SUM([Selected Month Sales]) – SUM([Previous Month Sales])

Format this to $ with 0dp too.

And we can also create

Colour – Diff > 0

[Difference in Sales] > 0

Pop all these into a table sorted by Difference in Sales ascending to see what it all looks like.

Solution 1 – Using sets

Right click on the State/Province field and create > set

Top States

using the Top condition to get the Top n States where n is the value from the pTop parameter, based on the Difference in Sales field.

and then create another set

Bottom States

using the Top condition to get the Bottom n States where n is the value from the pTop parameter, based on the Difference in Sales field.

Then right click on one of these states and create combined set

Top & Bottom

shows all members in both the Top States and the Bottom States sets

Add Top & Bottom to the Filter shelf. By default only the records ‘in’ either of the sets will display. Modify the pTop parameter to see the list change.

To build the viz

  • add State/Province to Rows
  • Add Difference in Sales to Columns and sort ascending
  • Add Top & Bottom to Filter
  • Add Colour – Dff > 0 to Colour and adjust accordingly.
  • Add Selected Month Sales to Tooltip and adjust
  • Show the pTop and pSelectedMonth parameters

To display the name of the month selected as a column header, create

Selected Month

[pSelectedMonth]

and add to Columns as a discrete (blue) pill at the month-year level. Hide field labels for rows and columns, remove all row/column dividers, zero lines, axis rules & tick marks.

Solution 2 – not using sets

This solution involves using the Rank table calculation

Create a new field

Asc

RANK_UNIQUE([Difference in Sales],’asc’)

and another called

Desc

RANK_UNIQUE([Difference in Sales],’desc’)

Revert back to the data table we built and remove the Top & Bottom set filter. Now add Asc and Desc to the output.

There are now two fields indexing each row; one which starts at 1 and increments, and the other that decrements so the last row is 1. We can now use these to filter just to the records we want

Records to Include

[Asc]<= [pTop] OR [Desc]<=[pTop]

Add this to the Filter shelf and set to True, and we can now see our top 12 and bottom 12 listed

Build the viz exactly as described above, but this time, add the Records to Include field onto the Filter shelf instead.

Pop these onto a dashboard and job done!

My published viz is here : Set Solution | Rank Solution

Happy vizzin’!

Donna

Can you create a normalised jitter plot?

Donna Coles1 Comment

Continuing the theme of alternative chart types, Kyle decided to challenge us to recreate this jitter plot inspired by an example from The Big Book of Dashboards.

I’ve built jitter plots in the past for #WorkoutWednesday challenges (the hidden RANDOM() function is your friend in this), but I wanted to see how far I could get without having to peak at my previous solutions.

So I connected to the baseball data provided, and cracked on, building the jitter in a single sheet using Measure Names on the columns. But then I got stuck when it came to labelling the tooltips…. surely this didn’t need a sheet per measure did it…

…maybe it did… so I proceeded to recreate as 4 separate sheets, and felt quite smug that I’d managed to make use of the ‘little used’ worksheet caption to provide the summary detail at the bottom of each measure. When I’d finished, I checked Kyle’s solution, as the summary values for the SLG & OPS measures seemed to be mixed up…. and what did I find…. he had managed to build the jitter within a single sheet as he had pivoted the data first! Argggghhhh! It just hadn’t crossed my mind, and Kyle had chosen not to drop that hint in the requirements…. hey ho! c’est la vie! I may well recreate with a pivoted version at a later date, but for now, I am blogging what I did…

My solution has ended up with a lot of calculated fields as a result, as equivalent fields needed to be created for every measure. Most of this was managed via duplicating and editing existing fields, so it actually wasn’t too onerous.

Building the calculated fields

We’ll start as usual by building out the fields required, and will focus on the BA measure initially.

Add Name and BA into a tabular view and Sort descending. Format the BA measure to be a number with 3 decimal places.

We need to know the rank of each player. Create a calculated field

Rank BA

RANK(SUM([BA]))

Add this to the view, and we should get the player rankings displayed from 1 downwards.

Now the requirement wants us to normalise the measures so they can be displayed on the same axis (or in my case, since it’s not a single chart I’m building), within the same axis range.

What this means is we want to plot the measure on a scale between 0 and 1 where 0 represents the lowest measure value, and 1 the highest. for this we need

Min BA

{FIXED :MIN([BA])}

and

Max BA

{FIXED :MAX([BA])}

The normalised value then becomes

Normalise BA

([BA] – [Min BA])/([Max BA]-[Min BA])

The difference between the current value and the lowest value, as a proportion of the range (ie the difference between the highest and lowest values).

Adding this to the table, you should see that the Normalise BA value for the highest ranked player is 1 and that for the lowest ranked is 0.

As part of the information displayed, we also need to know the percentile each player is in.

Percentile BA

RANK_PERCENTILE(SUM([BA]))

Format this to a percentage with 0 dp and add into the table.

Next we need to identify the player selected, so we’re going to create a parameter based off of the Name.

Select a Player

Right click Name -> create -> Parameter. This will open the parameter dialog and auto populate the list of options with the values from the Name field. Default the parameter to Julio Rodriguez.

We can then create a field to identify if the player is the one selected

Is Selected Player?

[Name] = [Select a Player]

Add this into the table, on the Rows before Name and sort so True is listed at the top (just easier to check the results).

So now we need to identify the rank and percentile of the selected player only

Selected Player BA Rank

WINDOW_MAX(IF ATTR([Is Selected Player]) THEN [Rank BA] END)

format this to a number with 0 dp

Selected Player BA Percentile

WINDOW_MAX(IF ATTR([Is Selected Player]) THEN [Percentile BA] END)

format this to a percentage with 0 dp.

The window_max function has the effect of ‘spreading’ the result over all the rows.

Finally we need to get a count of all the players

Count Players

{FIXED:COUNTD([Name])}

format this to a number with 0 dp.

Building the Jitter Plot

To build a jitter plot, we need to plot each mark against 2 axes. The Normalise BA measure is one axis, but we need to create ‘something’ for the other. This is the value to make the ‘jitter’ which is essentially an arbitrary value between 0 and 1 that we can plot the mark against, and means the marks don’t all end up in a single line on top of each other, and we can get a better ‘feel’ for the volume of data being represented.

Jitter

RANDOM()

The random() function is a ‘hidden’ function that will, as it’s name suggests, generate a random number. It is ‘hidden’ as it only works with some data sources. Excel for example is fine, but if you were connected to a Snowflake database, you can’t use it.

The nature of random, also means that you can’t guarantee the value it produces, and it will regenerate on data refresh, so if you’re looking to compare your solution directly, your dots will not be positioned exactly the same.

On a new sheet add Jitter to Columns and Normalise BA to Rows. Add Name to Detail and change the mark type to Circle.

Add Is Selected Player to Colour, adjust accordingly and add a border to the circle. I dropped the opacity to 70%. Order the colour legend, so True is listed first, to ensure this circle is always ‘on top’.

Then add Is Selected Player to Size. Edit the sizes so they are reversed and adjust the sizes until you’re happy.

To label just the selected player mark

Label:BA

IF [Is Selected Player] THEN [BA] END

format this with a custom number font ,##.000;-#,##.000

Add this to the Label shelf and adjust the font colour, and align centrally

Add Rank BA and BA to the Tooltip shelf and adjust tooltip to suit. You will need to adjust the table calculation setting of the Rank BA field so that it is computing by all the fields.

Add Selected Player BA Rank and Selected Player BA Percentile and Count Players to the Detail shelf. Adjust the table calculations as above (including any nested calcs), then show the worksheet caption (Worksheet -> Show Caption), and edit the caption to display the relevant text.

From the analytics pane, drag the Median with Quartiles option onto the canvas and drop it on the table / Normalise BA axis option. Remove the quartile reference lines (right cick axis -> remove reference line), and edit the median reference line to be a dashed line with no label.

Finally remove all gridlines/dividers/axes lines and hide the axes. Title the sheet as per the measure ie BA, and align centrally.

Format the Caption and the Title to have a light grey background and a slightly darker thin border.

Now, repeat all that for the other measures 🙂 This isn’t that bad. All the fields above labelled BA, need duplicating, renaming and updated to reference the next measure eg OBP.

Once done, duplicate the BA jitter plot sheet, and replace all the ‘BA’ related fields with the equivalent ones, by dragging the equivalent field and dropping it directly on top. Sense check the table calculation settings are all ok. You may need to update the text in the caption, as that seems to lose anything to do with the table calculation fields referenced when they get touched.

Ultimately you should end up with 4 sheets.

Putting it all together

On a dashboard, use a horizontal container to position all 4 sheets in side by side. Show the worksheet caption for each sheet. Reduce the outer padding for each sheet to 0, and add a thin border around each sheet.

Add a parameter action to drive the interactivity ‘on click’ of a circle

Select Player

On select of any of the source sheets, update the Select a Player parameter with the value from the Name field. Retain the selected value on ‘unclick’

To prevent the selection on click from being ‘highlighted’, and al the other marks ‘fading’, we need one final step.

Create new calculated fields

True

TRUE

False

FALSE

Add both these fields to the Detail shelf of each of the 4 sheets.

Then add a dashboard filter action for each sheet which on select, goes from the sheet on the dashboard to the worksheet itself, passing the selected fields of True = False. Show all values when unselected.

My published viz is here. Kyle’s solution with a lot less calculated fields, and only 2 sheets (1 for the jitter and 1 for the summary section at the bottom) is here. You will need to pivot the data via the data source pane first through 🙂 Next time, when I really feel something should be able to be done in 1 sheet, I’ll try to think a little longer…. upshot though, I impressed myself at the use of the caption for the summary – something I must consider using more often!

Happy vizzin’!

Donna

Adding Detail & Context

Donna ColesLeave a comment
It was Sean Miller’s turn to expand on last week’s #WOW2022 challenge, by adding an additional viz to the scatter plot (challenge details here).

The assumption is you should be able to build on the challenge solution you have built in the previous week. My solution to the Reference Box challenge is here. I adopted (and blogged about) a table calc solution. However, the published solution used LoDs. Both methods achieved the desired result, but I decided when starting this challenge, that I would build this ‘extension’ using LoDs too, so anyone who uses the published solution as a starting point, gets help via this blog.

So if you used my previous blog to build the challenge, you’ll need to first create the LoD equivalent of the calculated fields we used (note I did not change the scatter plot viz to use these fields) :

PR- 25th Percentile LoD

{PERCENTILE({FIXED [State]: SUM([Profit])/SUM([Sales]) },0.25)}

PR-75th Percentile LoD

{PERCENTILE({FIXED [State]: SUM([Profit])/SUM([Sales]) },0.75)}

Sales- 25th Percentile LoD

{PERCENTILE({ FIXED [State]: SUM([Sales]) },0.25)}

Sales- 75th Percentile LoD

{PERCENTILE({ FIXED [State]: SUM([Sales]) },0.75)}

Note – It’s also worth reiterating at this point, that I will be referencing calculated fields/parameters/objects in this blog created as part of my initial challenge.

In order to build the bar chart, we need to categorise each State into a grouping; the selected/highlighted state, the states in the reference box, all other states.

To identify the states in the reference box, we need to use FIXED LoDs to get the value of the Sales and Profit Ratio at the State level.

Sales by State

{FIXED [State]: SUM([Sales])}

PR by State

{FIXED [State]: SUM([Profit])/SUM([Sales])}

We can then use these fields along with the LoDs further above to determine whether a State is in the reference box

In Reference Box?

[PR by State]>=[PR-25th Percentile LOD] AND
[PR by State]<= [PR-75th Percentile LOD] AND [Sales by State]>=[Sales – 25th Percentile LOD] AND
[Sales by State]<= [Sales – 75th Percentile LOD]

This simply returns a boolean.

Now we can categorise each State

State to Display

IF [Selected State] THEN [State]
ELSEIF [In Reference Box?] THEN [State]
ELSE ‘Other (median)’
END

And with the above, we can then define the measures we want to show

Sales to Display

{FIXED [State To Display]: MEDIAN([Sales by State])}

PR to Display

{FIXED [State To Display]: MEDIAN([PR by State])}

And with these fields we can now build the bar chart.

  • Add Selected State to Rows and drag the dimension value, so True is listed before False.
  • Add In Reference Box? to Rows, and again drag so the True is listed before False.
  • Add State to Display to Rows
  • Add Sales to Display to Columns and Sort descending
  • Add PR to Display to Columns
  • On the All Marks Card add Selected State to Colour
  • Then add In Reference Box? to the Detail shelf. Then click on the … icon to the left of the In Reference Box? pill on the marks card, and change to the Colour icon. This should result in 2 fields on the Colour shelf.
  • Adjust the colours accordingly.
  • Add Sales to Display and PR to Display to the Tooltip shelf and adjust.
  • Change the titles of the axis
  • Remove row banding
  • Uncheck Show Header against Selected State and In Reference Band?
  • Hide field labels for Rows against the State to Display column heading

Add this sheet onto the dashboard and you’re done 🙂

My published viz is here.

Happy vizzin’! Stay Safe!

Donna

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