One of the key elements that users often look for in a sales dashboard is a solid set of KPIs. They give a quick, digestible snapshot of performance before diving deeper into the details.
Now I won’t go too in-depth on the technical setup of building KPIs and dynamic parameters mainly because I’ve already written a blog on that (shameless plug: check it out here). Instead, I’ll focus more on the reasoning behind the choices I made, and how I brought the first section of the dashboard together visually and functionally.
Also, if you haven’t read the earlier blogs in this series, I recommend starting reading these first to understand the full scope of the project and how we got to this point.
Part 1 - Building the parameter
For these KPIs, I wanted to showcase how a selected year performed compared to the previous one. The Superstore dataset includes data from 2021 to 2024. To ensure valid year on year comparisons, I set up my parameter to only include 2022 to 2024 (as 2021 has no prior year to compare against).
Once the parameter was set up, I created calculated fields that can dynamically adjust based on the selected year. After some testing creating labelled sheets (and a few minor tweaks), everything was behaving as expected (note: it is always worth checking early before moving forwards!)
Part 2 - Building the KPI Chart
The next stage was to build the KPI charts. When I was planning this graph out, I knew I wanted to have the value large, with the %difference between that value and the previous year as the title. Then for the graph itself, it would be spark lines showcasing the current year vs the previous year, as well as having the min and max values for the current year highlighted for quick analysis.
To do this, I simply first built a line chart displaying the current and previous year calculated field. I then created a calculated field that worked out the Min/Max values for the current year based on what was being displayed. This was done by using table calculations embedded within an if statement. Table calculations work by viewing the data shown on the pane and calculating an aggregation based on what has been selected. In this case, we wanted to look at the Min and Max values for the current year, and only display these values.
Once we get these values, we can then dual axis these values onto the visualisation, change mark to circle and then colour based on if it is a minimum or maximum value. We can then add the calculated parameter values to the title of the sheet, and do some formats to get it looking like this
Part 3 - Building the other charts
The final charts in this section looks at the sales and profits for the superstore broken down by different grouping, Segment, Region and Category before diving further into each of the sub categories within a category. As I wanted to look both at the sales and the profits, the graphs could either be done by incorporating a measure switch, or by creating 2 versions of the dashboard based on if they wanted to see sales or profit which they can swap between based on a button. I ended up opting for the latter but in hind sight, I might have been better to have gone with the measure switch however at that time, I was more comfortable with creating an action button that swaps to another dashboard.
To build these bars I simply added the current and previous year values to columns ensuring that both were selected as bar marks, and the grouping to the rows. For the previous year values, I sized the bar slightly wider than current year however set the colour transparency to 30%. I also chose to keep the colours consistent across all the bars but keep the headers to minimise double encoding.
And that's it for the graphs used in the overview dashboards. The next blog in this series will look into how I created the graph used in the orders summary as well as creating a summary orders table (albeit in a hacky way… but it works).
Thanks for reading! 😊
Victor