Calculated Value Using Parameter In Table (Ignoring Filtering)

Question & Answer
1

Hi Quicksight Community,

I wanted to ask for your advice on a problem using parameters in calculated fields within a table.

I built a table, that given a number of grouped columns, it averages the value of one of the columns on the right side of the table, given a specific Job ID.

Additionally, I want to implement a feature, whereby the user can select a different Job ID and compare the average value for that row side-by-side, as shown:

Screenshot 2023-07-21 at 9.49.21 AM
Screenshot 2023-07-21 at 9.49.21 AM2524×1384 250 KB

How could I implement this feature? Specifically what calculated value would I need so that the table would output the average value for the Job ID that the user selected, not the Job ID present in the table row?

Thank you!

1 Like
2

Hey @olivercarmont !

When you say that you want to compare the selected Job ID side by side, do you mean you want to compare it against another unique Job ID to compare their averages?

If so you could try a drop down list selection so that you choose the Job ID’s that you want to compare and the table will filter out all other rows.

1 Like
3

Hi @duncan,

Thank you for your response!

Sorry, I should’ve clarified. I already have a drop-down, I need a calculated field that can calculate the average of a column, given the selected job ID from that drop down and add it alongside the existing data.

In essence, for every row in the data (so not unique job IDs), I want to compare the average of a column, that has a different Job ID (from the drop-down)

Do you know I could write this calculated field? Thanks!

4

Hey @olivercarmont ,

Could you try something like avgOver(sum(Average Value), [Job ID Compared parameter])?

Let me know if that helps!

1 Like
5

Hi Duncan,

I tried it out. Unfortunately, it didn’t work, but luckily I found a calculated field that did!

For anyone else watching this thread, this calculated field worked:

avgOver( avgIf ((column, {job_id} = ${dropdown_parameter_value}), [ {column_1}, {column_2} ])

Where column_1 and column_2 are filters for the entire data that I’m also using in this field and job_id is a filter specific to this column.

Thanks for the help!