7. PivotTables and PivotCharts
7.3 Summarize Values By
Right-click a value › Summarize Values By › Sum, Count, Average, Max, Min, Product, More Options… (or Value Field Settings).
| Need | Put in Values | Summarize by |
|---|---|---|
| Sales | Amount | Sum |
| Number of order lines | Order ID | Count |
| Average delivery time | Delivery Mins | Average |
| Slowest delivery | Delivery Mins | Max |
| Number of distinct customers | Customer | Distinct Count (only when the pivot uses the Data Model) |
You can drag the same field twice into Values – e.g. Amount as Sum and Amount as Average (AOV per line).
Worked example. Rows: City; Values: Sum of Amount, Count of Order ID, Average of Delivery Mins → for Pune: ₹544, 4 orders, 11 mins.
Ravindra Bagale's Tip
Number column madhe ek jari text/blank cell asla tar PivotTable default Count ghete, Sum nahi – khup students "Count of Amount" la sales samajtat! Values madhe field takla ki heading bagha: "Sum of…" aahe ka? Nasel tar source madhle text-numbers clean kara (5.6).
Practice task
For each city show: total sales, number of orders, average and maximum delivery time, and (with the Data Model) the distinct count of customers.