Using Parameters in Calculated Fields

Hello, I'm Dylan, a cloud developer at West Loop Strategy, an AWS partner. As part of our learning series on parameters in QuickSight, this will be a tutorial on using those parameters with calculated fields to filter your visuals.

Sometimes the data we need to work with does not build into the most user-friendly filter and control components. If we wanted to make a drop-down to allow the reader to choose between different groupings of metrics that are not directly stated in our data, it might be worth building out your own groups and using the parameter value to determine what data will be returned in our visuals.

Let's say we want to build a sheet where the user could filter visuals based on the groupings of percent of sales as profit. We can create a calculated field that will give us that value by dividing our profit field by sales. Then we can make our percent of sales parameter which will be just a string value and we can set the default to one all. I'm going to use a number in front of each item in our list, so we can order the options exactly how we want. Otherwise, the list will be alphabetized no matter the order we enter them into the control. Now it is time to build out the options. You want the user to filter by the control, so we will create five options to select different groupings. The options I created are based on what I think will provide the most data per grouping related to the values in my Dataset.

Once completed, we now have a way we can use the percent of sales as profit calculated field to relate directly to our filter options. Now we can control what data is returned to a visual based on the user's parameter selection. The simple version of this would be filtering percent of sales by each order on a table. We will make one more calculated field that will check what value is being returned by the parameter. And if the value being returned by our calculated field matches the group, when that is true, we will return our calculated field value. Otherwise we return null. Now we can filter our visual by the final ifelse field we created, and require it to exclude NULL. This way, the data we want excluded from the groupings will not affect our visual and we get a user-friendly filter that can manage any of the metrics returned from our Dataset. All you need to do is return a Dataset field other than percent of sales in the EFL statement and make sure you filter out NULLs for that calculated field.

Thanks for joining today to find more exciting coverage of how we help our customers use AWS cloud technology or to schedule a QuickSight training session, visit our website and follow us on LinkedIn.