MSK Institute • Shikohabad
Loading Page Content...
Preparing courses, live labs, and learning resources.सामग्री लोड हो रही है, कृपया प्रतीक्षा करें...
Connecting serverPlease wait...
💻 Practical Labs🚀 Free Demo📜 Verified Certificates
Capstone: Applying Advanced Formulas, DAX, and Pivot KPIs0%
Capstone: Applying Advanced Formulas, DAX, and Pivot KPIs
Beginner12 min read•Updated: Jul 11, 2026
Capstone: Applying Advanced Formulas, DAX, and Pivot KPIs
Continuing our Business Performance Dashboard, we now author enterprise DAX Measures inside the Power Pivot Data Model and construct the analytical Pivot Tables that feed our executive visual layer.
1. Authoring Core DAX Measures
Open the Power Pivot Window (Power Pivot > Manage) and navigate to the Calculation Area below the Fact_Transactions table to author our KPI measures:
-- 1. Total Sales Volume
Total Sales := SUM(Fact_Transactions[GrossRevenue])
-- 2. Total Cost of Goods Sold (COGS) using SUMX Iterator
Total COGS := SUMX(Fact_Transactions, Fact_Transactions[UnitsSold] * RELATED(Dim_Products[UnitCost]))
-- 3. Gross Profit Margin
Gross Profit := [Total Sales] - [Total COGS]
Profit Margin % := DIVIDE([Gross Profit], [Total Sales], 0)
-- 4. Prior Year Sales (Time Intelligence)
Sales Last Year := CALCULATE([Total Sales], SAMEPERIODLASTYEAR(Dim_Calendar[Date]))
YoY Growth % := DIVIDE([Total Sales] - [Sales Last Year], [Sales Last Year], 0)
The Power of RELATED():
In measure #2, 'Fact_Transactions' does not contain 'UnitCost'. Using RELATED(Dim_Products[UnitCost]), DAX follows the established relationship to pull the unit cost from the dimension table in memory!
Click to Zoom2. Building Analytical Staging Pivot Tables
On a dedicated worksheet named Calc_Pivots, build four strategic Pivot Tables sourced from the Data Model:
- 1PT_SummaryKPIs: Contains '[Total Sales]', '[Gross Profit]', '[Profit Margin %]', and '[YoY Growth %]' formatted as Currency and Percentages.
- 2PT_MonthlyPerformance:
- Rows: 'Dim_Calendar[MonthName]'
- Values: '[Total Sales]' and '[Sales Last Year]'
- 1PT_RegionalProfitability:
- Rows: 'Dim_Customers[Region]'
- Columns: 'Dim_Customers[Segment]'
- Values: '[Total Sales]' and '[Profit Margin %]'
- 1PT_CategoryLeaderboard:
- Rows: 'Dim_Products[Category]' and '[SubCategory]'
- Values: '[Total Sales]' (Sorted Largest to Smallest).
3. Creating KPI Link Cells
In empty cells beside your Pivot Tables, format dynamic text summary cells to feed our dashboard cards:
- Revenue Card Value: '=PT_SummaryKPIs!B4'
- YoY Badge Text:
=IF(Calc_Pivots!B7>=0, "▲ +" & TEXT(Calc_Pivots!B7, "0.0%") & " YoY", "▼ " & TEXT(Calc_Pivots!B7, "0.0%") & " YoY")
Interactive Quiz
Multiple Choice Questions
Question 1
In our DAX measure, why did we use the RELATED function inside SUMX?
Question 2
Which DAX function calculates sales for the exact same calendar period in the previous year?
Question 3
Why is '=DIVIDE([Gross Profit], [Total Sales], 0)' superior to '=[Gross Profit] / [Total Sales]'?
Question 4
Where did we author our explicit DAX measures inside the Power Pivot window?
Question 5
What does our dynamic badge formula return if YoY Growth in cell B7 is +0.084?
Next Lesson
Capstone: Polishing the Interactive Dashboard with Macro Automation
Continue learning with hands-on practice, examples, and exercises in the upcoming topic.
Related Lessons
Practice Quiz
Test your understanding of this lesson with 5 questions. Each question has one correct answer.