Visual filter using calculated field

Hello all, need your help. In the attached image, Visual A uses calculated numbers in Visual B and Visual C. But the issue is that Visual B users an additional filter F1 to get the number 876 and Visual C doesn’t. Now when I pull the numbers to Visual A, the filter gets applied to both or neither. (1158 will become 876 if I apply filter F1, but it also gets applied to 165 and I do not want that). How can I create a calculation to mimic the visual level filter? Thanks.

IT sounds like you could use SumIf or CountIF to help. I do this all the time and it is very helpful.

counif(F1 = “my value”, B) / count(C)
sumif(F1 = “my value”, B) / sum(C)

or something similar.

without more information, I can’t be more specific you a specific example based on what you’ve got.

I do this all the time and it works well.

Thank you for the response. The issues is that the numbers I am trying to use are aggregates and it doesn’t work if I use sumif

instead of using a filter on B, would you be able to use a parameter and control? Then rather than linking B to a filter you could create a calculated field that changes the value of B based on the parameter (replacing F1). That way there is technically no filter so it would only affect 876 in both B & A but 165 would remain the same.

Yes. I often use the SumIF/CountIF in conjunction with a parameter or other field that I do not filter on. It all depends on what you are trying to do.

@rbrady , there is no issue with using filters on B or C. The problem is that, in A, I want one number (1158) after filter is applied (becomes 876), and the other number (165) I do not want filter on. But when I use a parameter or filter on A, it applies to both numbers.

That makes sense. I would suggest you get rid of the actual filters all together. Instead make your measure B, (the 1158/ 876 number) a calculated field based on a conditional statement. For example sumif some parameter = some value. That way it essentially filters only the value and not the actual visual. Which will be beneficial because then in visual A the 876/1158 number would be affected but the 165 would not be. This would only work if your parameter is single value, so I’m not sure if it applies in your situation but figured it was worth a shot.

That’s the tricky part. That measure is a combination of measures and is already aggregated, so sumif is not allowed :frowning:

awe bummer! I’m dealing with a similar trouble right now. I have a table that is filtered by date, but one of the fields in the table is supposed to return the value from the previous year. But since the previous year is filtered out the value always returns 0, frustrating! I’ll be following along to see if you find a solution, sorry I couldn’t be of more help.

Thank you. Please let me know if you find something.

1 Like

You can use some calculated fields in the dataset. Would it help, if you move some of your calculations to the dataset, so this way, you can aggregate them in the analysis with sumif?

Hi, @Ags. Did @thomask’s solution work for you? I am marking his reply as, “Solution,” but let us know if this is not resolved. Thanks for posting your questions on the QuickSight Community Q&A Forum!