Displaying multiple number formats using a single measure
Kellie Burton, Senior QuickSight Solutions Architect, AWS
This article focuses on how Amazon QuickSight provides the flexibility to build dynamic calculated fields with different number formatting. This approach can be used when your data is structured with a single measure field who’s type is identified by another dimension in the data. Another common use case is applying different currency formats to a single measure. This is useful to eliminate mouse clicks creating individual calculated fields for each measure. This approach minimizes the number of fields authors must search through to select calculations for their visuals. This is also valuable in situations where authors are approaching the maximum limit of 100 calculated fields in an analysis.
Use case description
In this scenario, we will be working with retail source data. The source data has one record per store per department with one measure field. The type of measure is identified by an additional dimension field (MetricType). If MetricType is 1, the measure value is sales quantity. If MetricType is 2, then the measure value is sales dollars. If MetricType is 3, then it is average checkout time in seconds.
Many times, the analyst will create three calculated fields, one for each metric type. However, QuickSight does provide the ability to create one dynamic calculated field to support the 3 different calculations by utilizing ifelse checks on the dimension field (MetricType). In this case, the 3 different measure types being created all need a different format. In order to support the display of the different formats, we will convert all values to strings. This will meet the display requirements in a table/pivot table view. However, you will not be able to aggregate on this new calculated field. In chart visuals, use the existing measure field with a format applied in the field well and a filter on MetricType.
Step 1 Create new calculated field for Measure based upon MetricType
See the calculation created for the calculated field (NewCalc) below.
NewCalc=
ifelse(
min(MetricType)=1, toString(sum(Measure)),
min(MetricType)=2, concat(‘$’,toString(decimalToInt(sum(Measure)))),
min(MetricType)=3, concat(
ifelse(sum(Measure) / 3600 < 10,concat(‘0’,tostring(floor(sum(Measure) / 3600))),tostring(floor(sum(Measure) / 3600))),‘:’,
ifelse((sum(Measure) % 3600)/60 < 10,concat(‘0’,tostring(floor((sum(Measure) % 3600)/60))),tostring(floor((sum(Measure)% 3600)/60))),‘:’,
ifelse(sum(Measure) % 60 < 10,concat(‘0’,tostring(floor(sum(Measure) % 60))),tostring(floor(sum(Measure) % 60)))
),
NULL)
The calculation first checks if the minimum value of MetricType is 1. If it is, then the value of the new calculated field is the sum of the Measure field and then that value is converted to a string. If MetricType is not equal to 1, the next ifelse condition is evaluated.
If the minimum of MetricType=2, a $ is concatenated to the sum of the Measure field to display sales dollars. That result is then converted from a decimal to an integer for display purposes.
If the minimum of Metrictype=3, then convert the seconds to HH:MM:SS format. This calculation involves nesting of ifelse to calculate the hours, minutes and seconds and then converting all values to strings and concatenating together separated by semicolons. An example of this calculation can be found on our QuickSight democentral site.
Finally, to account for any data issues, set the value of the new calculated field to null if the minimum of MetricType is not 1,2, or 3.
*Note: This calculation requires using a minimum aggregation for MetricType because QuickSight requires all components of the calculation to either be aggregated or non-aggregated. If you do not aggregate MetricType, you will get a mismatched aggregation error.
When you add the NewCalc field to a table visual, you will see the values displayed with the desired formatting. (shown in screenshot to the right)
This NewCalc field is a string so no aggregations can be made from this field.
Step 3 (Optional) Create the new calculated field for Metric Name
Create the Metric Name calculation shown below to enhance the display of the values in visualizations.
Metric Name
ifelse(MetricType=1,‘Sales Qty’,MetricType=2,‘Store Sales $’,MetricType=3,‘Avg Checkout’,‘no match’)
Step 4 Build a pivot table visual
Add a visual to your sheet and change it to a pivot table. Add Store and Department fields to the Rows field well. Add Metric Name to the Columns and the NewCalc to the Values field well. (see screenshot below). It is worth noting that when using the pivot table visual, if you collapse to store level in this example, you will see a sum aggregation across all columns since we are using one custom calculation.
In the format visual pane for the pivot table, select pivot options and check Hide single metric.
Step 5 Build a vertical bar chart visual
Add a visual to your sheet and change it to a vertical bar chart. Our NewCalc value is a string and works well for display in a table/pivot table. When creating charts a measure value is required. In the screenshot below, an example is shown on how to get the necessary display with this data format. The original Measure field in the data is used for the value and then a filter is applied to this visual for Metric Name. The filter allows only the sales quantity values to be included in the bar chart.
Step 6 (Optional) Build out additional visuals, publish and share dashboard.
The screenshot below shows a completed dashboard allowing users to view the key metrics in the appropriate formats and see them as KPIs and chart visuals. The banners were created using the Text visual with a background color.
Conclusion
This article demonstrated how QuickSight can support the creation of dynamic calculated fields with different display requirements. Creating the new calculation as a string allows the developer to produce the necessary display formatting in tables and pivot tables. The original measure can be used with a filter for visualizations that require aggregation.
