Question

Added field formula to AVG only rows with same value as current record

  • 16 November 2022
  • 1 reply
  • 9 views

I want to add a field that shows the AVG of records in my group, for example:

AVG ( IF ( country = mycountry, salery, NULL ) )

where “country” is a field in the table, and “mycountry” is the value of “country” in this specific row.

 

So all the rows with “country = france” will have “countryAVG = 10”

and all the rows with “country = denmark” will have “countryAVG = 12”

 

What is the correct syntax to achieve that? is that even possible in a field formula?

 


1 reply

Userlevel 7
Badge +1

Depends on how you want to visualise it. You could predefine averages for specific countries:

 

measure: avg_by_country {

  type: average | average_distinct

  sql: ${salary} ;;

  sql_distinct_key: …. ;; (if needed)

  filters: [country: “France”]

}

 

Or create one measure without the filter and then in your exploration select Country + your measure, it will aggregated average per country due to your context and granularity

Reply