For Week 7 of #WOW2024, Donna Coles has launched an exciting challenge: to build a custom control that allows users to include or exclude, according to their choice, any of the states in the Top 5 sales.
The main requirements of the challenge are:
- Create a custom control that displays, for each state, a bar chart reflecting its sales. Above each bar, the name of the state and the corresponding total sales will be shown. In addition, a circle will be added to the left of each bar, which will be filled or empty depending on whether the state is included or excluded.
- Modify the fill color of the sales bar to white when a state is excluded by the user.
- Create a chart that shows the cumulative sales per month of the top five states in sales, excluding those that have been indicated as excluded in the custom control menu.
After publishing my first solution for this challenge, I continued to reflect on it, and some additional questions arose when considering the possibility of states entering or leaving the Top 5 over time. For example:
- Should the states in the Top 5 be automatically selected by default?
- Would consistency be maintained in the colors used for the states across different charts when changing the Top 5?
- Would the color change of the bars to white, which is the designated color for excluded states, work correctly when a new state enters the Top 5 and is then excluded by the user?
These questions led me to work on a new version that could address these considerations that I had overlooked in my initial version.
To test this new strategy, I incorporated a filter that allows the user to select the region they want to consult the Top 5 for. This facilitates a straightforward verification of these considerations when the Top 5 changes.
Below, I outline the steps I followed in this new version.
STEP 1. DEFINE PARAMETER.
We will define a parameter to manage the inclusion or exclusion of states in certain charts, according to user preferences.
On this occasion, I propose modifying the conventional approach to handling this parameter. Instead of listing the states that are included, we will now use it to contain the values of those that are excluded. This modification ensures that all states are initially included by default in the queries, regardless of which are the top five states in sales.
| PARAMETER | TYPE | CURRENT VALUE |
| Parameter | String | | |
The parameter will be initialized with the character “|” which is the character we will use as a separator between each of the values stored in the parameter.
STEP 2. IDENTIFY IF THE STATE IS EXCLUDED.
If the state is listed within the parameter value, it will be interpreted as excluded in the queries (value TRUE); otherwise, it will be included in the queries (value FALSE).
| Is Exclude? | CONTAINS([Parameter], “|” + [State/Province] + “|”) |
STEP 3. DEFINE THE LABEL FOR THE TOOLTIP
We will set a label indicating whether clicking on the state implies its inclusion or exclusion.
| Label | IIF([Is Exclude?], “Click to include”, “Click to exclude”) |
STEP 4. DEFINE THE FIELD TO UPDATE THE PARAMETER VALUE.
In this step, we will define the field with which the parameter value will be updated if one of the options from the custom control is selected. This involves adding or removing the state’s name from the parameter content, depending on the user’s selection.
| Update Parameter | IIF([Is Exclude?], REPLACE([Parameter], “|” + [State/Province] + “|”, “|”), [Parameter] + [State/Province] + “|”) |
STEP 5. DEFINE THE TOTAL SALES PER STATE FIELD.
In this step, we will define a LOD calculation to obtain the total amount of sales accumulated per state.
| Total Sales | { FIXED [State/Province] : SUM([Sales]) } |
STEP 6. DEFINE COLOR FIELDS.
Two color fields will be defined, the usage of which will depend on the chart being worked on.
For the first definition, the color will be assigned based on the state’s position within the Top 5, regardless of whether the state is selected or not.
| Color Top 5 | “Top ” + STR(INDEX()) |
On the other hand, in the second definition, a specific and unique color will be used for the excluded states, while for the rest of the states, the color will be based on their position within the Top 5.
| Color with Exclude | IIF(ATTR([Is Exclude?]), “Exclude”, “Top ” + STR(INDEX())) |
STEP 7. DEFINE FIELDS TO AVOID OPTION HIGHLIGHTING.
In this step, we will establish the necessary fields to prevent the highlighting of options when selected within the custom control.
| HL | TRUE |
| UHL | FALSE |
STEP 8. CREATE AN OPTIONS SELECTION CONTROL.
The solution I have employed involves the definition of two charts for building the control. However, it is necessary to arrange these two charts properly in the control panel so that the user perceives them as if they were a single chart.
Definition of the bar chart in the control construction:
Add the “Region” field and select all options. Then, configure it as a context filter. Next, incorporate the “State/Province” field as a filter and configure it to show the Top 5 in sales.
Add the “State/Province” field to the rows shelf and sort it in descending order by sales.
Create a new field on the columns shelf with the formula “AVG(0.0)”.
Switch to using the color white and remove borders.
Add the “State/Province” and “Sales” fields on the text shelf and adjust to display them together on a single line.
Add the “Update Parameter” and “HL” fields on the detail shelf.
Add the “Sales” field to the columns shelf to create a second axis.
Remove the “State/Province” and “Sales” fields from the text shelf.
Add the “Label” field to the tooltip shelf.
Move the “Color with Exclude” field to the color shelf, adjusting the opacity to 50% and configuring it to automatically include the border. Adjust the table calculation according to the following definition:
Adjust to the desired colors.
Modify the parameter value to “|California|” so that the “Exclude” value appears in the color field and adjust the color for this value to white.
To continue, reset the parameter value back to “|”.
Add the “Measure Names” field to the rows shelf to allow the text with the state name and sales to be positioned above the bar chart when we switch to using a synchronized dual-axis, thus creating the effect of two rows. Adjust to use a synchronized dual-axis and hide the axes headers.
Now you can observe the visual effect of simulating two rows for each state.
Definition of the shape chart in the control construction:
We will duplicate the newly created worksheet to leverage some of the work done in defining the second chart.
In the first variable of the columns shelf (Definition of the first row), we will remove the “State/Province” and “Sales” fields from the text shelf to leave that first-row blank.
In the second variable of the columns shelf (Definition of the second row), we will edit the formula directly in the shelf, replacing SUM(Sales) with AVG(1.0). We will adjust the dual-axis to a range between 0.5 and 1.5 and hide the axes. Subsequently, we will change the chart type to “shapes” and adjust the color to 100% opacity.
Add the “Is Exclude?” field to the shapes shelf and adjust to the desired shapes. Proceed to hide the axes headers.
Replace the “Color with Exclude” field in the color shelf with the “Color Top 5” field and review the table calculation definition.
Check the consistency of the colors used between this chart and the previous one.
STEP 9. CREATE CUMULATIVE SALES CHART.
Add the “Region” field and select all options. Then, configure it as a context filter. Subsequently, incorporate the “State/Province” field as a filter and configure it to show the Top 5 of the average of total sales.
Move the “Is Exclude?” field to the rows shelf.
Modify the parameter value to “|California|” to ensure that both possible values of the “Is Exclude?” field exist, allowing for hiding records with a value of “TRUE”.
Once the “TRUE” value has been hidden, reset the parameter value back to “|”.
Add “Order Date” to the columns, adjusting it to display data by year and month. Convert to discrete field.
Add the “State/Province” field to the text shelf and adjust the text color to match the mark’s color.
Add the “Color Top 5” field to the color shelf, apply a 50% opacity to the color, and review the table calculation definition.
Check that the colors used match those defined in the previous charts.
Add the “Sales” field to the rows shelf and apply a table calculation of “Running Total”.
STEP 10. DEFINE PARAMETER ACTIONS.
| PARAMETER ACTION | SOURCE | TARGET PARAMETER | FIELD OR VALUE | AGGREGATION |
| Parameter | Bars & Menu | Parameter | Update Parameter | None |
STEP 11. DEFINE FILTER ACTIONS TO AVOID HIGHLIGHTING.
In the dashboard, we will use a filter action for each menu to prevent highlighting when selecting one of the marks.
We’re finished!
Link here.
Thank you very much, and if you have any questions about the post, feel free to contact me on Twitter ( @rosariogaunag )
RosarioViz 