topic Re: How to create Pivot Table like MS Excel in ArcGIS Dashboard in ArcGIS Dashboards Questions

How to create Pivot Table like MS Excel in ArcGIS Dashboard

HunarSaleem — Mon, 12 Jun 2023 13:33:01 GMT

I have big Excel sheet table, contains province (16 provinces A, B, C, D.....etc) and a column name Status. The Status has many categories like ( Canceled, Completed, Extended, Initiated, Issued, Ongoing, Suspended ).

In the ArcGIS dashboard I need to create a table like below:

Province	Canceled	Completed	Extended	Initiated	Issued	Ongoing	Suspended	Grand Total
A	66	139	55	0	183	11	3	457
B	5	11	1	0	20	2	1	40
C	13	25	0	0	7	0	0	45
D	36	529	107	3	1	632	0	1,308
E	13	37	0	0	39	0	0	89
F	22	55	11	0	88	1	3	180
G	0	10	0	0	12	0	0	22
H	3	130	6	0	0	154	0	293
I	2	57	10	0	0	66	0	135
J	6	11	0	0	16	0	0	33
K	142	331	51	4	331	18	25	902
L	2	13	0	0	7	1	0	23
M	21	63	9	1	74	10	1	179
N	5	58	1	0	0	23	0	87
W	6	39	2	0	27	0	0	74
Grand Total	342	1,508	253	8	805	918	33	3,867

How can I do that in ArcGIS Dashboard, please your support.

The Excel sheet is in the attachment file.

Re: How to create Pivot Table like MS Excel in ArcGIS Dashboard

JoseBarrios1 — Mon, 12 Jun 2023 14:08:32 GMT

Hello HunarSaleem

There are several ways to accomplish this on the current AGOL Dashboard platform.

1) use the table widget on dashboard. In table type, pick features (instead a grouped vales) and add your fields.

2) if the table is not too big, you can just copy and paste the table into a "Rich text" widget and edit layout with html/css.

3) if the table is big, you can also create the table using the "list" widget and enable arcade/html to build the table

4) the easy way will be to lunch the Experience builder app a create a page with your table (Exp. Builder does have a nice table widget) and use the "embedded content" widget in dashboard to bring the Exp. Builder table.

Re: How to create Pivot Table like MS Excel in ArcGIS Dashboard

HunarSaleem — Wed, 14 Jun 2023 11:52:24 GMT

Thank you JoseBarrios1 for your reply and suggestions, I already try all these ways that you suggested, but it doesn't gave me what I want. See the Excel file that I attached, and look at the result (Just like Excel Pivot Table) that I mentioned in my message. I want to be my table just like I illustrated.

Any other suggestions, please?

Re: How to create Pivot Table like MS Excel in ArcGIS Dashboard

MHami — Tue, 04 Mar 2025 23:15:29 GMT

Did you ever find a solution to this? I'm trying to do the same thing. Thanks!

Re: How to create Pivot Table like MS Excel in ArcGIS Dashboard

Katie_Macintyre_Atmos — Fri, 21 Nov 2025 11:23:59 GMT

I'm trying to do something similar with breeding bird territories. I'd like to summarise by species and breeding status but the grouped values option only gives the total count per species and doesn't allow the count to be split by the breeding status field. The features option suggested above doesn't give the total counts, just the features values themselves

Re: How to create Pivot Table like MS Excel in ArcGIS Dashboard

JoseBarrios1 — Fri, 21 Nov 2025 14:38:55 GMT

The standard ArcGIS Online Dashboard “Grouped Values” only gives totals per single field, and “Features” just lists each feature — so you need to use a data expression (Arcade) to create the summary table.

Arcade:

// Load the layer

var fs = FeatureSetByPortalItem(

Portal('https://www.arcgis.com'),

'70f5d3269a...your ID',

['Province', 'Status'],

false

);

// Get distinct values

var provinces = Distinct(fs, 'Province');

var statuses = Distinct(fs, 'Status');

// --- Build dynamic fields list ---

var fields = [

{ name: "Province", type: "esriFieldTypeString" }

];

for (var s in statuses) {

Push(fields, {

name: s.Status,

type: "esriFieldTypeInteger"

});

}

// Add grand total column

Push(fields, {

name: "GrandTotal",

type: "esriFieldTypeInteger"

});

// Build schema

var schema = {

fields: fields,

geometryType: "",

features: []

};

// Track totals for each status across all provinces

var overallTotals = {};

// Initialize overall totals

for (var s in statuses) {

overallTotals[s.Status] = 0;

}

// --- Loop through provinces and build rows ---

for (var p in provinces) {

var provName = p.Province;

var attrs = { "Province": provName };

var rowTotal = 0;

for (var s in statuses) {

var statusName = s.Status;

var cnt = Count(

Filter(fs, "Province = @provName AND Status = @statusName")

);

attrs[statusName] = cnt;

rowTotal += cnt;

overallTotals[statusName] += cnt;

}

// Add grand total column

attrs["GrandTotal"] = rowTotal;

// Push row

Push(schema.features, { attributes: attrs });

}

// --- Add GRAND TOTAL ROW ---

var finalRow = { "Province": "GRAND TOTAL" };

var finalSum = 0;

for (var s in statuses) {

var statusName = s.Status;

var totalStatus = overallTotals[statusName];

finalRow[statusName] = totalStatus;

finalSum += totalStatus;

}

finalRow["GrandTotal"] = finalSum;

// Add to the table

Push(schema.features, {

attributes: finalRow

});

return FeatureSet(schema);