This week’s #WOW2025 challenge was a guest post by Kieran Adair and had two parts to it; one to show Sales and Profit Ratio for 2024 only on the same chart, and the other to show comparisons to 2023 data on the same chart.

I built the first one way, and had to make changes in order to build the second. I could have chosen to rebuild the first chart using the fields I ended up creating for the second, but I chose not to, so this will ultimately be 2 blogs in one, as for each chart I needed different fields.

Restricting the data

We only need data for 2023 and 2024 for this challenge, so apply a data source filter (right click data source > Add data source filter) and restrict to Years 2023 and 2024 only.

Building the 2024 chart

We will be plotting Profit Ratio so need to create

Profit Ratio

SUM([Profit])/SUM([Sales])

format this to % with 0 dp.

Add Sub-Category to Rows and Profit Ratio to Columns. Add Order Date to Filter and restrict to the Year 2024 only. Sort the bars descending |(just click the icon on the toolbar)

Change the Mark type to circle and set the colour to black. Widen the rows a little.

Add Sales to Columns. Change the mark type of the sales marks card to bar. Set the colour to white and add a black border.

Adjust the Tooltip on the All marks card and verify both measures are displayed whether hovering over a circle or a bar.

Edit the Sales axis and set to custom to ensure it is fixed to start at 0 and end ‘automatic’. Adjust the axis title to include the text [Bars◻].

Make the chart dual axis BUT DO NOT synchronise axis. Remove Measure Names from the Colour shelf of the All marks card. Right click on the Sales axis at the to and Move marks to back.

Edit the title of the Profit Ratio axis so that it includes the text [Circles ●]. Remove all gridlines and zero lines and hide the Sub-Category row heading (right click the text and hide field labels for rows). Update the title and name the sheet RAVE 2024 or similar.

Building the YoY chart

For this version, we need to create some additional calculated fields

Sales – 2024

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

Sales 2023

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

Profit – 2024

IF YEAR([Order Date]) = 2024 THEN [Profit] END

Profit 2023

IF YEAR([Order Date]) = 2023 THEN [Profit] END

PR – 2024

SUM([Profit – 2024])/SUM([Sales – 2024])

format to % with 0dp

PR – 2023

SUM([Profit – 2023])/SUM([Sales – 2023])

format to % with 0dp

On a new sheet, add Sub-Category to Rows and PR – 2024 to Columns. Sort descending. Change the mark type to circle.

Drag PR – 2023 onto the PR -2024 axis and release the mouse when the 2 green column symbol appears.

This will automatically update the viz to include Measure Names and Measure Values. Adjust the Colour legend so PR 2024 is black and PR 2023 is grey, and PR 2024 is listed first (so black circles are on top when the marks overlap)

Add Sales 2024 to Columns. Change the mark type of the Sales 2024 marks card to bar. Remove Measure Names from the Colour shelf of the Sales 2024 marks card. Set the colour to white and add a black border.

Add Sales – 2023 to the Detail shelf of the Sales 2024 marks card. Right click on the Sales 2024 axis and Add Reference Line per cell referencing the Sales 2023 value as below.

Update the title of the Sales – 2024 axis to Sales [Bars◻] and set the axis to be fixed from 0 to automatic. Update the title of the other axis (Value) to Profit Ratio [Circles ●].

On the All marks card add PR 2023, PR 2024, Sales – 2023 and Sales -2024 to the Tooltip shelf and update to suit.

Make the chart Dual Axis but once again DO NOT synchronise the axis. As before move the marks (bars) of the Sales axis to the back, and remove the Sub-Category label. Remove all gridlines and zero lines and update the title.

And that’s it. I added both my sheets to dashboards, juts to improve the presentation slightly. My published viz is here.

Happy vizzin’

Donna