I created a calculated field in order to show specific values for many countries in the table below. What I would like to do is compare the value from the USA specifically, to the rest of the countries listed in the table.
So, I created a calculated field using an ifelse statement to show just the country USA, but of course the other countries show as blank since it has been filtered in the calculation, as seen in the dimension “Value to compare with”.
As a quick and temporary solution, since this value ends up being the maximum of any other countries values, I created a max calculated dimension, where that same value finally replicates in all other countries rows (dimension “Max Value”).
The issue with that is I won’t be able to edit any of the filters, since the max value might change to another country, and I’d just like to compare any of the countries, just to the US, whichever the filters used.
So what would be the best way to compare a filtered value with an ifelse() statement (here the USA) to other unfiltered rows?
Or would there be a way to “record” the calculated value for the USA into a parameter, then use that parameter for calculations, comparison with other dimensions, etc. I want this to be a variable that will always change based on filters used, but specifically for the USA.
Many thanks for the reply, I did have a look at the demo you shared and tried the couple of examples if that could be applied here.
The issue with my case is that I’m trying to compare a single field in Values, for different Rows (Country Name), but all examples proposed always compare different fields in Values (in the sandbox you shared: Regional Sales, and total Sales), to the same row (Company).
So I tried attributing a parameter to the Country (USA) I wanted to compare to the rest, but the issue is that I’ll always have to create a calculated field with an ifelse() statement, in order to compare that parameter to the rest. And that ifelse statement won’t allow the comparison with any other rows. Using PREFILTER with LAC aggregation doesn’t work either unfortunately.
I think one of the solution would be to record that specific value to a parameter, like using a variable in programming, so that we could do calculations on top of that, regardless of any filters used in the calculated fields. Perhaps there is a different way to do that without having to filter the countries, but haven’t found any other ways than using the maxOver for now…which works but can vary depending on the filters used on the visual.