In this blog, we will provide you with a detailed, step-by-step guide to tackle the Week 39 challenge of #WOW2023, presented by Valerija Kirjackaja and Erica Hughes.
Within the context of this challenge, we are faced with the need to create a dynamic table that will break down sales and assess target attainment. It is essential to highlight that monthly targets are defined at the subcategory level. However, our aspiration is to empower users to disaggregate sales along various additional dimensions, such as region, segment, and shipping mode.
This approach is carried out with the objective of identifying the relative contribution of each of the possible values of these dimensions, even knowing in advance that there is no sales target defined at such a deep level of detail.
The key requirements of this challenge are as follows:
- Allow the selection of a month.
- Facilitate the selection of an additional dimension (region, segment, or shipping mode) to break down the table. The column header for the additional dimension should update dynamically.
- Display:
- Sales amounts for each category, subcategory, and selected additional dimension.
- The percentage of sales, solely at the level of the selected additional dimension.
- The sales target amount and the variation versus the sales target at the category and subcategory level (The variation will be colored blue if the target is met and orange if it is not).
Among the requirements, one of the most notable aspects is the need to dynamically update the column header corresponding to the additional dimension. To resolve this particularity, a common approach involves overlaying a text object on top of the generic column title within the dashboard, displaying the selected parameter’s value. However, there is an alternative technique that allows for this update directly within the table definition, employing a clever approach.
In this approach, the designated row for “Grand Totals” in the table is utilized. However, instead of using it to display cumulative totals, it will be used to present the required dynamic headers.
Next, I will share the detailed steps to construct this solution!
STEP 1. DEFINE THE DATA MODEL.
In this project, we will employ a relational model to combine Superstore information with sales targets. The relationship between both tables will be established at the category, subcategory and month level.
STEP 2. DEFINE PARAMETER.
We will use a parameter to allow the user to select the additional dimension to display in the table.
| PARAMETER | TYPE | ALLOWABLE VALUES | CURRENT VALUE |
| Breakdown by | String | Region, Segment & Ship Mode | Region |
STEP 3. DEFINE THE BREAKDOWN FIELD DEPENDING ON THE USER SELECTION.
| Breakdown | Dimension | CASE [Breakdown by] WHEN ‘Region’ THEN [Region] WHEN ‘Segment’ THEN [Segment] WHEN ‘Ship Mode’ THEN [Ship Mode] END |
STEP 4. DEFINE THE FIELDS FOR THE TABLE HEADERS.
Create fields intended to store the header texts of each column, with a formula that guarantees that said texts are only visible when it comes to the ‘Grand Totals’ row.
| Breakdown | Header | IIF(COUNTD([Category]) != 1, [Breakdown by], ”) |
| Sales $ | Header | IIF(COUNTD([Category]) != 1, ‘Sales ($)’, ”) |
| Sales % | Header | IIF(COUNTD([Category]) != 1, ‘Sales (%)’, ”) |
| Target $ | Header | IIF(COUNTD([Category]) != 1, ‘Target ($)’, ”) |
| Variance % | Header | IIF(COUNTD([Category]) != 1, ‘Variance (%)’, ”) |
STEP 5. DEFINE THE FIELDS FOR THE TABLE DETAILS.
Generate individual fields for each column in order to reflect the corresponding value in all other rows, that is, those that are not considered ‘Grand Totals’. These formulas must be adjusted based on the different aggregation levels of the table to display the desired information at each aggregation level.
| Breakdown | Label | IIF(COUNTD([Breakdown | Dimension]) = 1, ATTR([Breakdown | Dimension]), IIF(COUNTD([Category]) = 1, ‘Total’, ”)) |
| Sales $ | IIF(COUNTD([Category]) = 1, SUM([Sales]), NULL) |
| Sales % | IIF(COUNTD([Breakdown | Dimension]) = 1, SUM([Sales]) / TOTAL(SUM([Sales])), NULL) |
| Target $ | IIF(COUNTD([Category]) = 1 AND COUNTD([Breakdown | Dimension]) != 1, SUM([Sales Target]), NULL) |
| Variance % | IIF(COUNTD([Category]) = 1 AND COUNTD([Breakdown | Dimension]) != 1, ((SUM([Sales]) – SUM([Sales Target])) / SUM([Sales Target])), NULL) |
| Variance % | Color | IIF(COUNTD([Category]) = 1 AND COUNTD([Breakdown | Dimension]) != 1, SIGN([Variance %]), 0) |
STEP 6. BUILD THE INFORMATION TABLE.
Place the ‘Month’ field from the Target table in the filter shelf and choose the individual dates option. Then, filter out the month of September 2023.
Add the fields ‘Category’, ‘Sub-Category’ and ‘Breakdown | Dimension’ to the lined shelf. Deselect the ‘Show Header’ option from the ‘Breakdown | Dimension’.
In the column shelf, directly insert the following formula: AVG(0). Set a fixed range on the axis from 0 to 0.5 and remove grid lines from the columns.
Select the ‘Shape’ chart type and within the ‘Shape’ shelf, choose a transparent shape.
Add the ‘Breakdown | Header’ and ‘Breakdown | Label’ to the text shelf. Customize the text so that both fields are displayed on a single line and use a bold font for the header. Select the ‘Middle – Left’ alignment.
In the ‘Analysis’ menu, select the ‘Total’ option. Within this submenu, choose ‘Show Columns Grand Totals’, ‘Columns Totals to Top’ and ‘Add all Subtotals’.
In the column shelf, directly insert a new formula: AVG(0.4). Sets a fixed range on the axis from 0 to 0.5.
Replaces the fields ‘Breakdown | Header’ and ‘Breakdown | Label’ in the text shelf, for the fields ‘Sales $ | Header’ and ‘Sales $’ respectively.
In the column shelf, directly insert a new formula: AVG(0.4). Sets a fixed range on the axis from 0 to 0.5.
Add the fields ‘Sales % | Header’ and ‘Sales %’ in the text shelf. Customize the text so that both fields are displayed on a single line and use a bold font for the header. Select the ‘Middle – Left’ alignment.
In the column shelf, directly insert a new formula: AVG(0.4). Sets a fixed range on the axis from 0 to 0.5.
Add the fields ‘Target $ | Header’ and ‘Target $’ in the text shelf. Customize the text so that both fields are displayed on a single line and use a bold font for the header. Select the ‘Middle – Left’ alignment.
In the column shelf, directly insert a new formula: AVG(0.4). Sets a fixed range on the axis from 0 to 0.5.
Add the fields ‘Variance % | Header and ‘Variance % ’ in the text shelf. Customize the text so that both fields are displayed on a single line and use a bold font for the header. Select the ‘Middle – Left’ alignment.
Additionally, in the Font, select the ‘Match Mark Color’ option.
Add the field ‘Variance % | Color’ to the color shelf and change the definition to discrete value. Adjust colors for orange, blue and black.
Hide field labels for Rows.
Customize the general totals label to simulate the heads of 2 dimensions -> Category Sub-Category
Hide axes and adjust formats.
Ready!
Link here.
If you have any questions or comments about the exercise, please don’t hesitate to get in touch.
RosarioViz 