How to create Pivot Table like MS Excel in ArcGIS Dashboard

[HunarSaleem]

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.

[JoseBarrios1]

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.

[HunarSaleem]

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?

[MHami]

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

[Katie_Macintyre_Atmos]

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

[JoseBarrios1]

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',

    0,

    ['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);