7. PivotTables and PivotCharts
7.6 Calculated Fields and Calculated Items
Calculated Field in a PivotTable with example — in 30 seconds
What: A Calculated Field is a new value field made from other fields, for example Amount after a 2% commission.
Why: You get the new number for every row of the pivot without touching the source data.
Tiny example: Net After Commission =Amount*0.98: Pune ₹544 becomes ₹533.12.
Calculated Field in a PivotTable with example — 30 सेकंदात
काय: Calculated Field म्हणजे इतर fields पासून बनवलेलं नवं value field, उदा. 2% commission नंतरचा Amount.
का: Source data ला हात न लावता pivot च्या प्रत्येक row साठी नवा number मिळतो.
छोटं उदाहरण: Net After Commission =Amount*0.98: Pune चे ₹544 होतात ₹533.12.
Calculated Field in a PivotTable with example — 30 सेकंड में
क्या: Calculated Field यानी दूसरे fields से बना नया value field, जैसे 2% commission के बाद का Amount.
क्यों: Source data को छुए बिना pivot की हर row के लिए नया number मिल जाता है.
छोटा उदाहरण: Net After Commission =Amount*0.98: Pune के ₹544 बन जाते हैं ₹533.12.
First look at the data (before → after):
Expected result — Pivot with the calculated field
| Row Labels | Sum of Amount | Sum of Net After Commission |
|---|---|---|
| Nashik | 360 | 352.80 |
| Pune | 544 | 533.12 |
| Solapur | 1,299 | 1,273.02 |
About this data: Teaching sample for a fictional shop “Pearl Textiles”. Figures and names (e.g. Aamir Khan) are made up for this lesson on calculated fields and calculated items.
Before table and formula as text
Before — Pivot: Sum of Amount (3 cities shown)
| Row Labels | Sum of Amount |
|---|---|
| Nashik | 360 |
| Pune | 544 |
| Solapur | 1,299 |
About this data: Demo table only — not real customer or company data. Built to practise calculated fields and calculated items.
Formula / click path
PivotTable Analyze › Fields, Items, & Sets › Calculated Field…
Name: Net After Commission · Formula: =Amount*0.98
Do it step by step:
- Click inside the pivot.
- Click PivotTable Analyze › Fields, Items, & Sets › Calculated Field….
- Type the name
Net After Commission. - In Formula, type
=Amount*0.98. - Click Add, then OK. A new column appears in the pivot.
स्टेप बाय स्टेप करून बघा:
- Pivot मध्ये click करा.
- PivotTable Analyze › Fields, Items, & Sets › Calculated Field… वर click करा.
- नाव
Net After Commissiontype करा. - Formula मध्ये
=Amount*0.98type करा. - Add करा, मग OK. Pivot मध्ये नवा column येतो.
स्टेप बाय स्टेप करके देखें:
- Pivot में click करें.
- PivotTable Analyze › Fields, Items, & Sets › Calculated Field… पर click करें.
- नाम
Net After Commissiontype करें. - Formula में
=Amount*0.98type करें. - Add करें, फिर OK. Pivot में नया column आता है.
- Calculated Field – a new value built from other fields: PivotTable Analyze › Calculations › Fields, Items, & Sets › Calculated Field…
- Calculated Item – a new item inside a row/column field (e.g. "Pune + Nashik"): Fields, Items, & Sets › Calculated Item… (select an item of that field first).
Steps in Excel – commission and AOV
- Calculated Field… › Name
Net After Commission› Formula=Amount*0.98(fictional 2% payment commission) › Add › OK. - For AOV, first add a column
Order Count=1to tblOrders (each row = one order) and refresh. - Calculated Field… › Name
AOV› Formula=Amount/'Order Count'› OK. Format as ₹.
Important: a calculated field works on the sums of fields, not row by row. =Amount/'Order Count' = Sum of Amount ÷ Sum of Order Count – correct for AOV. But a formula like =Qty*Unit Price would multiply the sums, which is wrong – do row-level maths in the source Table instead.
Calculated items can't be used when the same field has grouped items, and they can make totals confusing; prefer grouping (7.5) where possible.
Ravindra Bagale's Tip
Many students think a calculated field works row by row, write =Qty*Price, and get a wrong, huge total. Remember: a calculated field works on the sums. Do row-level calculations in a calculated column in the Table, and use a calculated field for ratios (AOV, margin %).
Ravindra Bagale's Tip – मराठी
Calculated field row-by-row चालतो असं बरेच students समजतात आणि =Qty*Price लिहून चुकीचा, खूप मोठा total मिळवतात. लक्षात ठेवा: calculated field sum वर चालतो. Row level calculation Table मध्ये calculated column मध्ये करा, आणि ratio (AOV, margin %) साठी calculated field वापरा.
Ravindra Bagale's Tip – हिंदी
बहुत से students सोचते हैं कि calculated field row-by-row चलता है, =Qty*Price लिखते हैं और गलत, बहुत बड़ा total पाते हैं. याद रखो: calculated field sum पर चलता है. Row level calculation Table में calculated column में करो, और ratio (AOV, margin %) के लिए calculated field इस्तेमाल करो.
Practice task
Add calculated fields Net After Commission and AOV (with an Order Count column). Compare AOV per city with a manual SUMIFS/COUNTIFS check.
Loading practice tasks...
- Total billable hours (Easy): Add up Billable hours in the "Billable hours" column.
- Average rate per hour (Easy): Average of the Rate per hour ($) column, rounded to 2 decimals.
- Rows in Cloud (Easy): How many rows have Practice "Cloud"?
- Largest billable hours (Easy): The largest value in the "Billable hours" column.
- Billable hours for Cloud (Medium): Total of the Billable hours column where Practice is "Cloud".
- Cloud in Chicago (Medium): Total of the Billable hours column where Practice is "Cloud" and City is "Chicago".
- Best in Cloud (Medium): Largest value in the "Rate per hour ($)" column where "Practice" is "Cloud".
- Value of row 2 (Medium): Multiply the first-row Billable hours by the first-row Rate per hour ($).
- Look up Sophie Martin (Medium): The Billable hours for "Sophie Martin" (use a lookup on the "Name" column).
- Total value (Hard): Sum of "Billable hours" times "Rate per hour ($)" for all rows.
- Share of Cloud (Hard): Share of the "Billable hours" column that is "Cloud", rounded to 3 decimals.
- Total "Billable hours" for SAP (Hard): Add up the "Billable hours" column for the rows where "Practice" is "SAP".
- Above average? (Hard): "Above" if the "Rate per hour ($)" value in the first row is above the average of the "Rate per hour ($)" column, else "Below".
Quick revision cards
Three standalone cards. Each one explains the idea with its own example. Scan the QR to open this lesson.
