fx Analysis & Visualization

Pivot Tables Deep Dive

90 मिनट7 पाठ21 प्रश्न

पाठ

1. Rows / Columns / Values / Filters — the logic of the 4 boxes13 min 2. Value Field Settings: % of total, % of parent, running total, rank, difference from13 min 3. Grouping: dates (month/quarter/year), numeric bins, manual groups13 min 4. Calculated Fields and Calculated Items — and their traps13 min 5. Slicers and Timelines, connecting one slicer to multiple pivots13 min 6. Pivot Charts13 min 7. GETPIVOTDATA — building a report cell from a pivot12 min

अभ्यास फ़ाइल

⬇ डाउनलोड करें analysis-visualization-practice.zip

Analysis & Visualization practice bundle

Extract the ZIP before opening files. Start with pivot-practice.xlsx: its Sales sheet contains the original 16-row tblSales table for pivot and dynamic-array exercises. Grand total: ₹10,70,000. Keep an unchanged copy as your baseline.

capstone/data/ contains 24 monthly sales CSVs, totaling 50,000 synthetic orders from April 2024 through March 2026. Stores, Products and Targets CSVs sit outside that folder so a sales-only folder import does not combine incompatible schemas.

analysis-reference.xlsx contains a Wide Targets exercise and independently calculated reference summaries for checking your work. These are plain tables, not completed PivotTables or a Power Pivot model; build those in Excel as directed in the lessons. solutions-en.md and solutions-hi.md provide exercise answers.

calendar.csv contains daily dates for 2024–2026, FY labels and fiscal month numbers. Load it into the model and mark its Date column as the date table. answer-key.json and the optional standard-library audit_capstone.py let you reconcile the CSVs without Excel. README.md explains setup and filter scope.

Use a supported Windows desktop Excel edition with Power Pivot for the complete model exercises. The modern dynamic-array lessons specify their own Excel version requirements. The download contains no macros, credentials, or external connections.

मॉड्यूल प्रश्नोत्तरी (21)

1. Which box limits the whole pivot to one salesperson?

Filters

2. A text field in Values shows what?

Count

3. Shortcut to refresh a pivot?

Alt + F5

4. Share of each category within its own region?

% of Parent Row Total, or % of Row Total with Category in Columns

5. Base item for month-on-month change?

(previous)

6. What's needed for Distinct Count?

Add the data to the Data Model

7. Pivot Qtr1 means which months?

Jan–Mar

8. Most common cause of "Cannot group that selection"?

Blank or text values in the date/number field

9. Two years of data grouped only by Months — what goes wrong?

Same months of both years merge

10. Calculated fields are evaluated on what?

The sums for each pivot cell, not individual rows

11. Is Margin % = Profit/Amount safe as a calculated field?

Yes — it's a ratio of totals

12. Main danger of a calculated item?

It's included in totals, causing double counting

13. How do you make one slicer filter three pivots?

Report Connections

14. Why might a pivot not appear in Report Connections?

It's built on a different data source

15. Which filter is designed for date ranges?

Timeline

16. In a pivot chart, the Columns area becomes what?

Series / legend

17. How do you remove the grey filter buttons?

Field Buttons → Hide All

18. Name a chart type that can't be a pivot chart.

Scatter, histogram, waterfall, etc.

19. Second argument of GETPIVOTDATA?

Any cell inside the pivot

20. Why is GETPIVOTDATA safer than =B5?

It finds the value by field/item names, not position

21. Cause of #REF! from GETPIVOTDATA?

The item/field isn't visible in the pivot