topic Re: Include sum and average in bar chart using data expression/arcade in ArcGIS Dashboards Questions

Include sum and average in bar chart using data expression/arcade

dwold — Fri, 08 Mar 2024 14:44:08 GMT

I am trying to write a data expression that finds the count of that field. Then calculates the average of a field (filter/selection dependent) and use that in a bar chart along with the sum of another field.

In the picture below, I am trying to get the Target Goal to be 12 (24/2 records) while the Target Value (14) is the sum of 2 values.

var fs = FeatureSetByPortalItem( Portal("https://www.arcgis.com/"), "384158f2ebe34f8ba8b0b39dfe5f5af5", 0, [ 'Jurisdiction', 'core_capability', 'target', 'target_goal', 'target_value_final'], false ); var tar_goal = $feature.target_goal var tar_final = Filter(fs, "target_goal = @tar_goal") //find the total count of tar_final var tar_count = Count(tar_final) //find sum of target_goal var goal_sum = Sum($feature, tar_goal) //Divide the sum of target_goal by target goal count var targets = goal_sum/tar_count return targets

@JohannesLindner

Re: Include sum and average in bar chart using data expression/arcade

jcarlson — Fri, 08 Mar 2024 15:47:18 GMT

Remember, you need to return a FeatureSet from a Data Expression. Also, in a Data Expression, there is no global $feature.

You can get the count and sum both out of a GroupBy function, and even do some derived values involving multiple fields.

The tricky part is "filter/selection dependent". Aggregating in a Data Expression is going to break any kind of per-feature identification in the data.

Are you wanting the "goal" value to come from the selected feature? I don't believe there's a way to pipe values into a data expression from the dashboard's interactivity.

Re: Include sum and average in bar chart using data expression/arcade

dwold — Fri, 08 Mar 2024 20:42:48 GMT

Are you wanting the "goal" value to come from the selected feature? - yes but it isn't a deal breaker for my case. I am trying to find the average of target_goal and use that as a field in my bar chart. If I do group by would it look something like this? Perhaps I am doing this all wrong.

var fs = FeatureSetByPortalItem( Portal("https://www.arcgis.com/"), "384158f2ebe34f8ba8b0b39dfe5f5af5", 0, [ 'Jurisdiction', 'core_capability', 'target', 'target_goal', 'target_value_final'], false ); var goal_group = GroupBy(fs, 'target_goal', {name: 'Goal', statistic: 'Count'}) var value_group = GroupBy(fs, 'target_value_final', {name: 'Goal', statistic: 'Sum'}) var end_value = value_group / goal_group return end_value