In WorkOut Wednesday 2022 Challenge 48, the task objective is to create a bar chart that groups different states based on a specified sales threshold percentage. The description of this challenge can be found in the following link:
While there are multiple requirements in this challenge, the key highlight in this challenge is the grouping of different states. A parameter, a sales percentage calculation and a conditional statement are needed to group the different states based on a specified threshold. Figure 1 shows the resulting visualisation of the WorkOut challenge.
Figure 1. Visualisation of WorkOut 2019 Week 48
To set a benchmark for the grouping, a parameter named ‘Group states contributing less than’ was created. Figure 2 shows the configuration of the parameter.
Figure 2. Configuration of parameter ‘Group states contributing less than’
After setting the parameter, a sales percentage (% of Sales) calculation and a conditional statement were made to group the different states. An initial configuration of these 2 aspects are shown in figure 3a and figure 3b.
(a)
(b)
Figure 3. Initial configuration of (a) % of Sales calculation and (b) State Grouping
As observed in figure 3b, the initial configuration of % of Sales calculation in figure 3a leads to errors in calculation. The errors occurred due to having the ‘% of Sales’ calculated field as an aggregated value being compared with parameter ‘Group states contributing less than’ as a non-aggregated value. Hence, adjusting the aggregation level of ‘% of Sales’ becomes essential to avoid the errors. In this scenario, the FIXED function was applied to adjust the aggregation level to the same level of detail (LOD). Further details of the revised solution can be seen in figure 4.
(a)
(b)
Figure 4. Revised configuration of (a) % of Sales calculation and (b) State Grouping
From figure 4b, it was found that adjusting aggregation level of ‘% of Sales by State’ in figure 4a leads to a valid calculation. The validity of the calculation occurs due to having both ‘% of Sales by State’ calculated field and ‘Group states contributing less than’ parameter in the same level of aggregation.
In summary, it is essential to evaluate the level of data aggregation to avoid any potential errors in creating new calculated fields. Creating LOD calculation through using the FIXED, INCLUDE or EXCLUDE functions can bring data to the desired level of aggregation.