Topic 23 — Compare Precalculated Ratios vs DAX Ratios
Create comparison measures that compare precalculated ratios from vw_All_Financial_Ratios against DAX-calculated ratios in Power BI.
Production / Cybersecurity Warning
This exercise uses synthetic training data. Do not connect Power BI to production financial systems, corporate databases, or real accounting data without authorization, sandbox testing, least-privilege access, change-control approval, and cybersecurity compliance.
Objective
Create comparison measures that compare precalculated ratios from vw_All_Financial_Ratios against DAX-calculated ratios in Power BI.
Use ratios precalculated in Python and SQL Server.
Recalculate selected ratios dynamically in Power BI using DAX.
Business Scenario
A good BI model should be auditable. This topic compares two architectures: ratios calculated before Power BI and ratios calculated dynamically inside Power BI.
| Output | Purpose |
|---|---|
| Precalculated ratio measures | Topic output |
| DAX ratio measures | Topic output |
| Variance measures | Topic output |
| Validation flags | Topic output |
Step-by-Step Practice
Step 1 — Connect Power BI to SQL Server
Use Power BI Desktop to connect to Server: JCDCOMPUTER and Database: FinancialRatiosBI.
Get Data → SQL Server\nServer: JCDCOMPUTER\nDatabase: FinancialRatiosBI\nMode: ImportStep 2 — Import the Required Tables and Views
Use the SQL views for reporting and the dimension/fact tables when DAX calculations require base financial statement values.
Dim_Company\nDim_Period\nFact_Income_Statement\nFact_Balance_Sheet\nFact_Cash_Flow\nFact_Debt_Ratios\nvw_All_Financial_Ratios\nvw_Executive_Financial_Summary\nvw_Latest_Period_RatiosStep 3 — Create or Confirm Relationships
Connect the company and period dimensions to the imported fact tables and views.
Dim_Company[CompanyID] → Fact and View tables[CompanyID]\nDim_Period[PeriodID] → Fact and View tables[PeriodID]Step 4 — Add the DAX Measures
Create a new measure table named DAX Financial Measures and paste the measures below.
-- ============================================================
-- Financial Ratios Analysis in BI
-- Topic 23 - Compare Precalculated Ratios vs DAX Ratios
-- ============================================================
-- ------------------------------------------------------------
-- Helper Measures for Precalculated Ratios
-- Source: dbo.vw_All_Financial_Ratios
-- ------------------------------------------------------------
Precalc Current Ratio =
CALCULATE(
AVERAGE(vw_All_Financial_Ratios[RatioValue]),
vw_All_Financial_Ratios[RatioName] = "Current Ratio"
)
Precalc Quick Ratio =
CALCULATE(
AVERAGE(vw_All_Financial_Ratios[RatioValue]),
vw_All_Financial_Ratios[RatioName] = "Quick Ratio"
)
Precalc Cash Ratio =
CALCULATE(
AVERAGE(vw_All_Financial_Ratios[RatioValue]),
vw_All_Financial_Ratios[RatioName] = "Cash Ratio"
)
Precalc Net Profit Margin =
CALCULATE(
AVERAGE(vw_All_Financial_Ratios[RatioValue]),
vw_All_Financial_Ratios[RatioName] = "Net Profit Margin"
)
Precalc Return on Assets =
CALCULATE(
AVERAGE(vw_All_Financial_Ratios[RatioValue]),
vw_All_Financial_Ratios[RatioName] = "Return on Assets"
)
Precalc Return on Equity =
CALCULATE(
AVERAGE(vw_All_Financial_Ratios[RatioValue]),
vw_All_Financial_Ratios[RatioName] = "Return on Equity"
)
Precalc Debt Ratio =
CALCULATE(
AVERAGE(vw_All_Financial_Ratios[RatioValue]),
vw_All_Financial_Ratios[RatioName] = "Debt Ratio"
)
Precalc Operating Cash Flow to Sales =
CALCULATE(
AVERAGE(vw_All_Financial_Ratios[RatioValue]),
vw_All_Financial_Ratios[RatioName] = "Operating Cash Flow to Sales"
)
-- ------------------------------------------------------------
-- Variance Measures
-- ------------------------------------------------------------
Current Ratio Variance =
[DAX Current Ratio] - [Precalc Current Ratio]
Quick Ratio Variance =
[DAX Quick Ratio] - [Precalc Quick Ratio]
Cash Ratio Variance =
[DAX Cash Ratio] - [Precalc Cash Ratio]
Net Profit Margin Variance =
[DAX Net Profit Margin] - [Precalc Net Profit Margin]
Return on Assets Variance =
[DAX Return on Assets] - [Precalc Return on Assets]
Return on Equity Variance =
[DAX Return on Equity] - [Precalc Return on Equity]
Debt Ratio Variance =
[DAX Debt Ratio] - [Precalc Debt Ratio]
Operating Cash Flow to Sales Variance =
[DAX Operating Cash Flow to Sales] - [Precalc Operating Cash Flow to Sales]
-- ------------------------------------------------------------
-- Absolute Variance Measures
-- ------------------------------------------------------------
Current Ratio Absolute Variance =
ABS([Current Ratio Variance])
Net Profit Margin Absolute Variance =
ABS([Net Profit Margin Variance])
Debt Ratio Absolute Variance =
ABS([Debt Ratio Variance])
-- ------------------------------------------------------------
-- Validation Flags
-- ------------------------------------------------------------
Current Ratio Validation =
IF(
ABS([Current Ratio Variance]) <= 0.01,
"Match",
"Review"
)
Net Profit Margin Validation =
IF(
ABS([Net Profit Margin Variance]) <= 0.01,
"Match",
"Review"
)
Debt Ratio Validation =
IF(
ABS([Debt Ratio Variance]) <= 0.01,
"Match",
"Review"
)
Overall Ratio Validation =
VAR Issues =
IF([Current Ratio Validation] = "Review", 1, 0)
+ IF([Net Profit Margin Validation] = "Review", 1, 0)
+ IF([Debt Ratio Validation] = "Review", 1, 0)
RETURN
IF(
Issues = 0,
"Precalculated and DAX ratios aligned",
FORMAT(Issues, "0") & " ratio area(s) need review"
)
Step 5 — Validate the Result
Create cards, tables, and trend visuals to confirm that the measures respond to Company, Industry, Fiscal Year, and Quarter slicers.
Expected Output
Comparison measures created.
Variance measures available.
Validation flags show Match or Review.Business Interpretation
This topic turns the model into an audit exercise. Students learn that a BI dashboard should not only display numbers; it should also prove that the numbers make sense.
Final Result of Topic 23
The Power BI model now supports a stronger BI architecture: stored ratios for auditability and dynamic DAX ratios for interactivity.
Next topic: Topic 24 — Build Dynamic Financial Ratio Cards.