Calculating accuracy metric using stdevpOver

I have a dataset with the following attributes: Country, City (which aggregates into country), Ship Method Type, Ship Method (which aggregates into ship method type), year, week, date, hour, prediction and actual value.
From this dataset, I want to calculate an accuracy metric which is: abs(prediction - actual) / stdev (deviation of the ship method type) - represented in the screenshot below:

On Quicksight, I want to display Week → Expanding into Country → Expanding into City → Expanding into Ship Method Type → Expanding into hours. The values displayed would be: Sum(Prediction), Sum(Actual) and Metric.

Then, if the user colapses the Ship Method Type, the values displayed are: Sum(Prediction), Sum(Actual) and Average(Metric) of the hours. Which, from the example, for MOSC1 would be the: Sum(I2:I25), Sum(J2:J25) and Average(L2:L25).

I have tried the following:

  1. Create a calculated field of partition_actual = sumOver(actual,[{country},{city},{date},{ship_method_type}],PRE_AGG)

  2. Create a calculated field of partition_prediction = sumOver(prediction,[{country},{city},{date},{ship_method_type}],PRE_AGG)

  3. Create a calculated field of partition stdev = stdevpOver(abs({partition_actual }-{partition_prediction}), [country, {city},{ship_method_type},{gh_in_date}],PRE_AGG)

  4. Create a calculated field of metric = abs({partition_actual }-{partition_prediction })/{stdev}


  1. stdev values don’t add up to calculations performed manually on excel.
  2. On field 4) metric, the numerator is x2 the intended value.
  3. If I collapse the Ship Method Type, the values of sum(prediction) and sum(actual) return 0.

How can I calculate my metric field and reach my intended goal?

Thank you so much for all the help in advance!

Hi @jmaido

Let me message you privately to get additional details.