fx Analysis & Visualization

Power Pivot & DAX Intro

75 min5 lessons15 quiz questions

Lessons

1. Data Model and relationships — multiple tables without VLOOKUP15 min 2. Measures vs calculated columns15 min 3. CALCULATE — the most important DAX function15 min 4. SUM vs SUMX (row context vs filter context)15 min 5. Time intelligence: YTD, MTD, same period last year15 min

Practice file

⬇ Download 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.

Module quiz (15)

1. Which side of a relationship must have unique values?

The "one" side — the dimension

2. Where do you draw relationships?

Power Pivot → Manage → Diagram View

3. Why mark a table as a Date Table?

So time-intelligence functions work correctly

4. Where is a measure calculated?

For each pivot cell, using that cell's filters

5. Function that brings a dimension value into a fact row?

6. Why DIVIDE instead of /?

Returns blank, not an error, when dividing by zero

7. What does CALCULATE change?

The filter context the measure is evaluated in

8. Which function removes filters for % of total?

ALL / REMOVEFILTERS

9. How do you make a filter intersect with the cell's filter instead of replacing it?

KEEPFILTERS

10. What does SUMX do differently from SUM?

Evaluates an expression row by row, then sums

11. Error "a single value for column cannot be determined" means?

A bare column used in a measure with no row context

12. What creates a row context?

Calculated columns and X-functions like SUMX, FILTER

13. What does "3/31" in TOTALYTD do?

Sets the year-end to 31 March, for an Apr–Mar FY

14. Function for "same period last year"?

SAMEPERIODLASTYEAR, inside CALCULATE

15. Why must pivots use Calendar fields?

Time functions work through the marked Date table