Dashboard Data Expression for Average

[KatieSelfEd]

Hello - I am using Dashboard to present some metrics from a map. The data set features buildings with data such as the campus they're in, the electric usage and floor area. One of the metrics I want to display is aggregated average electricity use at campus area (i.e. sum up the total electrical usage over features within a campus and divide by the area footprint of buildings in the same campus). I've been trying to use data expressions to do this but have been struggling as I haven't used Arcade much, was hoping someone could help.

So far I've used the GroupBy (see below) to see a table of aggregated values by campus, but I'm not sure how to then add a field and caluclate the use by square meter.

Don't think this is the best method regardless as ideally the filter on the dashboard will select the campus and the expression will just return (sum of all selected electricity use) / (sum of selected area).

var portal = Portal('https://www.arcgis.com');
var fs = FeatureSetByPortalItem(
    portal,
    '14635dbc307947efa58a1b363feb43a5');

var CampusAgg = GroupBy(fs,['Campus_1'],[
    {name:'Campus_Floor_Area', expression:'Floor_Area', statistic: 'SUM'},
    {name:'Campus_Electricity_kWh', expression:'Electricity_kWh', statistic: 'SUM'},
    ]);

return CampusAgg;

[kdoshiwork]

Joining two Feature Sets together and getting a co... - Esri Community

May be this post might help you.

I was able to pull the results I wanted for my dashboard by running a loop and a inner loop

[jcarlson]

I would agree that it's probably not the best approach, if you want the values to be filtered later on. Keep in mind that an Indicator or Chart widget can handle the aggregation for you. But you'll still need to get the electrical usage per square foot as its own "field", since (average(a) / average(b)) is not the same as average(a/b).

There are two ways to do this. The pure data expression way would be to loop over your input featureset and calculate that new field.

var out_dict = {
    fields: [
        {name: 'Floor_Area', type: 'esriFieldTypeDouble'},
        {name: 'Electricity_kWh', type: 'esriFieldTypeInteger'},
        {name: 'usage_per_sqft', type: 'esriFieldTypeDouble'},
        {name: 'Campus', type: 'esriFieldTypeString'}
        // plus whatever other fields you need in the output
    ],
    geometryType: '',
    features: []
}

for (var f in fs){
    Push(
        out_dict[features],
        {
            attributes: {
                Floor_Area: f['Floor_Area'],
                Electricity_kWh: f['Electricity_kWh'],
                Campus: f['Campus'],
                usage_per_sqft: f['Electricity_kWh'] / f['Floor_Area']
            }
        }
    )
}

return FeatureSet(Text(out_dict))

An easier way, however, is to use GroupBy, but in a slightly different way. Creating a new field is a bit easier at times using SQL in GroupBy, and by "grouping" by a unique field, we get the same features as the input, but with our added information. Since each unique ID only has a single feature, taking the "sum" gives us our input values back unchanged.

var CampusAgg = GroupBy(
    fs,
    ['objectID', 'Floor_Area', 'Electricity_kWh'], // put in all the fields you want to retain in the output
    {name: 'usage_per_sqft', expression: 'Electricity_kWh / Floor_Area', statistic: 'SUM'}
)