This week it was Kyle’s turn to add a 3rd piece to the dashboard (see challenge here).

I chose to build on my previous week’s solution, which you can download from here, so all fields referenced etc will be based on that (ie I may reference fields that don’t exist in the solution published by the #WOW crew, and that have not been created as part of this stage of the solution).

For this part of the challenge, we’re looking to add a trend line, which displays a line for each row in the bar chart above, which means we need to display the median values of the Sales and Proft Ratio measures, since the ‘Other’ bar represents a group of multiple states.

We also need to colour and size the lines according to whether the bar chart has been clicked on or not.

First up, to build the line chart, we need to create some new LoD fields, as the ones we have so far have been FIXED at a State level, and now we need to consider the month of each order. I’m essentially going to repeat the steps I used when building the bar chart, but this time as the month of the Order Date into the calculation

Sales by State & Date

{FIXED [State], DATETRUNC(‘month’, [Order Date]): SUM([Sales])}

PR by State & Date

{FIXED [State], DATETRUNC(‘month’, [Order Date]): SUM([Profit])/SUM([Sales])}

From these, I can then create the measures I need for the display

Sales by Display State & Date

{FIXED [State To Display], DATETRUNC(‘month’, [Order Date]): MEDIAN([Sales by State & Date])}

format this to $ with 0 dp

PR by Display State & Date

{FIXED [State To Display], DATETRUNC(‘month’, [Order Date]): MEDIAN([PR by State & Date])}

format this to % with 0 dp

I can then build the initial line chart, with Order Date on Columns, set to the continuous month level (eg May 2020) and Sales by Display State & Date and PR by Display State & Date on Rows. Add State To Display to Detail.

Now we need to colour and size the lines based on a combination of whether the State is the one selected in the scatter plot, or whether the user has clicked on one of the bars.

I decided that I would capture the ‘state’ the user selected on the bar into a new parameter. So I created

pSelectedStateFromBar

A string parameter defaulted to ‘blank’

With this, I can the create

Size – Line

[State To Display] = [pSelectedStateFromBar]
OR
[Selected State]

This returns true if the State To Display field matches the value captured in the parameter, or the (existing) Selected State field is true.

Add this to the Size shelf on the All Marks card and adjust so the True option is slightly thicker that the False option. You may need also need to adjust the slider against the Size shelf to get the thickness just right.

We also use these 2 parameters to determine what the colour of the line needs to be

Colour – Line

IF [Selected State] AND ([pSelectedStateFromBar]=” OR [pSelectedStateFromBar]=[State]) THEN ‘dark’
ELSEIF [State To Display]=[pSelectedStateFromBar] THEN ‘mid’
ELSE ‘light’
END

Add this onto the Colour shelf. You’ll never have all 3 options displayed at the same time, so you’ll need to set the pSelectedSateFromBar to empty and to a value in order to adjust the colours. You’ll also need to ensure ‘dark’ is listed above ‘light’ and then ‘mid’ is listed above ‘light’ so these lines appear ‘on top’.

The chart just needs tidying up now – edit/remove the axis titles, reduce the size of the text on the axis, remove gridlines and set the tooltips.

Once done, you’re ready to add to the dashboard.

I rearranged my dashboard to have a horizontal layout container to with the scatter plot in the left hand column, and a vertical container in the right hand column. The vertical container then had the bar chart above the line chart.

The final step was to the then add a parameter action that on ‘select’ of the bar chart, it passed the State to Display value into the pSelectedStateFromBar parameter.

And that should be it. My published viz is here.

Happy vizzin’! Stay Safe!

Donna