For the final week of global recognition month, Shunta Nakjima set this challenge inspired by one of the ‘founders’ of #WorkoutWednesday, Andy Kriebel.

Let’s get stuck in, by starting with the selector sheets.

Building the Measure Selector

The measure selector will be used to set a parameter which will store the particular measure selected, so we need

pSelectedMeasure

string parameter defaulted to the value Sales

We also need a field to help us ‘draw’ the 3 selection boxes onto a viz, in such a way that we then have a measure value to pass into the parameter on selection. You could build this with its own separate dataset, but I’m going to utilise another field to ‘fake this’, and drive it off the Segment dimension as we don’t need this in the rest of the viz.

Measure Selector Alias

CASE [Segment]
WHEN ‘Consumer’ THEN ‘Sales’
WHEN ‘Corporate’ THEN ‘Profit’
ELSE ‘Quantity’
END

Add Segment to Columns. Then double click into Columns and manually type MIN(1). Widen the row and then add Measure Selector Alias onto Label.

Edit the axis, and fix the range to run from 0 to 1. Adjust the label font and align centrally. Hide the axis and the Segment header.

We need to identify which measure has been selected, both through colour and an arrow indicator. So we need

Is Measure Selected

[Measure Selector Alias] = [pSelectedMeasure]

Add to the Colour shelf and adjust to suit.

Then create

Measure Selected Arrow

IF [Is Measure Selected] THEN ‘►’ END

I use this site to get the characters I need.

Add this to the Label shelf and adjust so the fields are side by side and the arrow text is coloured red.

Finally, stop the Tooltip from displaying, then hide all gridlines, zero lines, axis and row dividers. Set the column dividers to be thick white lines and make the Size as large as possible.

Name the sheet Measure Selector.

Building the Year Selector

In order to not ‘hardcode’ the latest year, we need

Current Year

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

format this to be a number with 0dp and not to show the thousands separator.

From this we can create

Comparison Year

IIF(YEAR([Order Date])<>[Current Year],YEAR([Order Date]),NULL)

On a new sheet, add Comparison Year to Filter and exclude NULL. Then add Comparison Year to Columns and sort descending, so the latest year is listed first. Double click into Columns and manually type MIN(1).

As before widen the rows, and then add Comparison Year to Label. Edit the axis, and fix the range to run from 0 to 1. Adjust the label font and align centrally. Hide the axis and the Comparison Year header.

We’re going to need a parameter which will capture the year selected

pSelectedYear

integer parameter defaulted to 2022 with the display format set to not include the thousand separator

We need to identify which year has been selected, both through colour and an arrow indicator. So we need

Is Selected Year

[Comparison Year] = [pSelectedYear]

Add to the Colour shelf and adjust to suit.

Then create

Year Selected Arrow

IF [Is Selected Year] THEN ‘►’ END

Add this to the Label shelf and adjust so the fields are side by side and the arrow text is coloured red.

Finally, stop the Tooltip from displaying, then hide all gridlines, zero lines, axis and row dividers. Set the column dividers to be thick white lines and make the Size as large as possible. Name the sheet Year Selector.

Building the Current Year ‘card’

Double click into Columns and manually type MIN(1). Add Current Year to Label. Widen the row. Edit the axis, and fix the range to run from 0 to 1. Adjust the label font and align centrally. Hide the axis. Adjust the Colour to suit. Stop the Tooltip from displaying, then hide all gridlines, zero lines, axis and row dividers. Make the Size as large as possible. Name the sheet Curr Year.

Building the bar chart

Based on the measure selected, we need to get the value of the relevant measure for the current year and for the comparison year, so we need

Measure to Display – Curr Year

IF YEAR([Order Date]) = [Current Year] THEN
CASE [pSelectedMeasure]
WHEN ‘Sales’ THEN [Sales]
WHEN ‘Profit’ THEN [Profit]
ELSE [Quantity]
END
END

and

Measure to Display – Comp Year

IF YEAR([Order Date]) = [pSelectedYear] THEN
CASE [pSelectedMeasure]
WHEN ‘Sales’ THEN [Sales]
WHEN ‘Profit’ THEN [Profit]
ELSE [Quantity]
END
END

On a new sheet, add Sub-Category to Rows and Measure to Display – Curr Year to Columns. Sort descending. Then click and drag Measure to Display – Comp Year to the axis and release when the two green columns display. This will automatically add Measure Names and Measure Values into the view.

Reorder the pills in the Measure Values box, so the current year values are listed first. Add Measure Names to Colour and adjust to suit. Add Current Year, Measure to Display – Curr Year and Measure to Display – Comp Year to Tooltip, and adjust the tooltip so it is referencing those 3 fields along with the pSelectedMeasure and pSelectedYear parameters

So we have the bars, but now we need to add the ‘candlestick’, which we’re going to crate using a gantt bar. We need another ‘measure’ row to show, and need another instance of Measure to Display – Comp Year for this – we can’t use the existing measure, as it will put data on the same ‘row’. So simply duplicate the Measure to Display – Comp Year field, to get the Measure to Display – Comp Year (copy) field. Add this to Columns.

Change the mark type of this to a gantt bar. To get the size and the information we need for the labels and to colour the gantt, we need some more fields.

Difference

SUM([Measure to Display – Curr Year]) – SUM([Measure to Display – Comp Year])

custom format this to +#,##0;-#,##0 and add this field to both the Size and the Label shelf.

% Difference

IF (SUM([Measure to Display – Curr Year])>=0 AND SUM([Measure to Display – Comp Year])>=0)
OR (SUM([Measure to Display – Curr Year])<0 AND SUM([Measure to Display – Comp Year])<0) THEN
[Difference]/ABS(SUM([Measure to Display – Comp Year]))
ELSE 0
END

If both the values are positive or both the values are negative, then calculate the difference, otherwise return 0, and then custom format to ▲0.0%;▼0.0%;- . The first section up to the ; formats the number when it’s positive, the next section formats when negative, and the last section formats when the number is 0, so in this case we’re replacing any 0 with a ‘-‘. Add this to the Label shelf.

Diff is +ve

[Difference]>0

Add this to the Colour shelf (remove Measure Names) and adjust accordingly.

Reduce the Size of the gantt bar, adjust the label so the font is smaller, organised as required, and aligned to the right. Remove all the text from the Tooltip. Then make the chart dual axis and synchronise the axis. Explicitly set the mark type of the Measure Names marks card to a bar.

Finally tidy up by hiding both axis, removing all gridlines, axis lines, zero lines and column dividers. Format the Sub-Category label headings and align middle left. Hide the Sub-Category row heading (hide field labels for rows) and hide the Measure Names field (uncheck show header). Name the sheet Chart.

Adding the interactivity

Using vertical and horizontal containers, arrange the objects on the dashboard. I used a horizonal container to align the Curr Year, Year Selector and Measure Selector sheets, adding blank objects in between. I edited the title of the 3 objects to display the required text. I then floated a blank object over the Current Year box, so it couldn’t be clicked.

To select the year and the measure, I needed parameter actions

Select Year

on select of the Year Selector sheet, set the pSelectedYear parameter, passing in the value from the Comparison Year field

and

Select Measure

on select of the Measure Selector sheet, set the pSelectedMeasure parameter passing in the value from the Measure Selector Alias field

Finally to stop the years and measure boxes being ‘highlighted’ on click, create fields

True

TRUE

and

False

FALSE

and add both to the Detail shelf of the Year Selector and Measure Selector sheets. Then add dashboard filter action

Deselect Years

On select of the Year Selector sheet on the dashboard, target the Year Selector sheet itself, passing the values True = False.

Create another similar filter action for the Measure Selector sheet, and that should then be it!

My published workbook is here.

Happy vizzin!

Donna