Using Calculated Field in IfElse Condition

Question & Answer
, ,
1

I have a set of data with two.fields and want to compare the value of each record to 1.5 times the average of the values for that group. For example, assume a table with the following information:

Category | Item | Value
———————————-
A | Z1 | 1
A | Z2 | 1
A | Z3 | 1
A | Z4 | 0
A | Z5 | 1
B | Z6 | 1
B | Z7 | 1
B | Z8 | 0
C | Z9 | 1
C | Z10 | 8
C | Z11 | 1

The output I am aiming for is taking the average value, by category, and then compare the value of each item to that average * 1.5 and then count how many items exceed that average * 1.5 threshold.

The output for the above might be something like:

Category | Avg * 1.5 | Count ( >=Avg * 1.5)
————————————————————
A | 1.2 | 0
B | 1.0 | 2
C | 5.0 | 1

I’ve tried a number of calculated field options, but feel like I’m stuck at this point without having made any progress. Any suggestions on how to accomplish this? Thanks!

1 Like
2

Hello @Doc, welcome to the QuickSight community!

I think I know how we would build that calculated field. I will write an example below:

Over Target = ifelse((avgOver({Value}, [{Category}], PRE_AGG)*1.5) <= {Value}, {Item}, NULL)

This would return each of the items where the value exceeds the threshold. Now if you want to figure out the number of times the threshold is exceeded, do you want that value to reset for each category or to be totalled across all categories? I’ll nest both functions so you can do either:

sumOver(distinctCountOver({Over Target}, [{Category}], PRE_AGG), [], PRE_AGG)

This function distinctCountOver({Over Target}, [{Category}], PRE_AGG) will give you the total per category that exceed the value, then you can nest that in a sumOver to get the total across all categories. One thing to note, if you want to use these sumOver/avgOver/minOver calculated fields in a visual, aggregate them as a min or max value in the field well. That will ensure you are seeing the correct value.

I’ll mark this as the solution, but please let me know if you have any remaining questions.

1 Like