Hello, I need to build a pivot table in QuickSight, and I’m seeking advice on how to structure a dynamic calculation.
Objective:
Rows: A selection of countries defined by a parameter [${CountrySelection}]
Columns: The result of the calculation avg(price) / avg(price, [${baseCountry}]).
I intend to read the selected countries’ positioning as an index of the base country
Context:
The parameter ${baseCountry} is defined by the user and represents one of the countries from the selection.
Challenge: I am encountering difficulties constructing the calculated field for the desired result. Specifically, I’m facing challenges in creating a dynamic calculation that adjusts based on the selected ${baseCountry} parameter.
Formula Attempted:
avg(price) / avg(price, [${baseCountry}])
However, I’ve run into errors, and there are no values resulting from this calculated fields besides when for baseCoutry (= 1)
Any clue on how to achieve this ?
(as I post this message on Dec 31st, my best wishes of a happy new year for all of you)
Hi Bek-
Did you solve your issue? If not, here’s a few thoughts. First, I can get a parameter to successfully work in the group variable using this formula: avg(Sales,[${country}]). It appears you are trying to take the avg of all countries and then divide it by the avg of the selected country in the parameter. I would assume that parameter is driving a filter on your visual. If so, the avg (price) in the numerator of your visual is only taking the avg price of the data in the visual (which is filtered to that single country). If you still need assistance, can you share more details so we can help. Thanks!
actually in numerator I have a selection of 10-12 countries, each individually displayed in their respective row, and in column is the corresponding avg(sales) for each country
now, in denominator, I am aiming at using a Parameter controled by my Users. This parameter selects the avg(sales) value of one of the 10-12 countries.
as a result all 12 countries are compared to the chosen one.
if with the control/parameter, my User selects CountryA as a base, then INDEX column becomes
countryA = 100
countryB = 110
countryC = 95
…
if User changes the base, and selects countryC as a base, then the Index column switches to
A = 105
B = 116
C = 100
D = 132
I struggle to make it work. I start to have doubts about the filters I added or the setting of my paramater.
Hello @bek, were you able to resolve the issue you were facing in QuickSight? If not, I think utilizing LAC-W functions might work better for this situation.
If your filter is altering the values that you are wanting to use, this could help you achieve the output without being impacted by the visual filter. It would look something like this:
You can also try swapping PRE_AGG with PRE_FILTER to see if there are still issues with the result. Another consideration, add the numerator and denominator as their own calculated fields as well and include them on the table to see if you are getting the expected values for each. That will help target the issue and will provide some more information. Let me know if this helps!
Thank you for your suggestion. I’m facing a similar problem here, but I would like to change the column name “price”. Do you know if it is possible to create a dynamic measure, something like SUM(<ColumnNameParameter>)? I work in a very dynamic environment where we don’t know which columns will appear in our dataset. That’s why I need a dynamic calculation.
