Financial Ratios Analysis in BI

Step-by-Step Practice

Step 1 — Prepare the folder and load previous files

Step 2 — Build topic-specific calculations

Step 3 — Export the output CSV or documentation file

Step 4 — Validate rows, keys, and financial logic

Step 5 — Interpret the result as a BI analyst

Script / Instructions

Copyfrom pathlib import Path
import pandas as pd

# ============================================================
# Financial Ratios Analysis in BI
# Topic 18 - Power BI Model Preparation
# ============================================================

# ------------------------------------------------------------
# Step 1 - Set project folder
# ------------------------------------------------------------

base_path = Path("financial_ratios_bi_training")

if not base_path.exists():
    raise FileNotFoundError(
        "The project folder does not exist. Please run Topic 1 first."
    )

print(f"Project folder found: {base_path.resolve()}")

# ------------------------------------------------------------
# Step 2 - Define Power BI model tables
# ------------------------------------------------------------

model_tables = [
    {
        "TableName": "Dim_Company",
        "FileName": "Dim_Company.csv",
        "TableType": "Dimension",
        "LoadToPowerBI": "Yes",
        "PrimaryKey": "CompanyID",
        "Description": "Company master table with company, industry, ticker, and profile attributes.",
        "RecommendedImportOrder": 1
    },
    {
        "TableName": "Dim_Period",
        "FileName": "Dim_Period.csv",
        "TableType": "Dimension",
        "LoadToPowerBI": "Yes",
        "PrimaryKey": "PeriodID",
        "Description": "Fiscal period table from 2021 Q1 to 2025 Q4.",
        "RecommendedImportOrder": 2
    },
    {
        "TableName": "Dim_Account",
        "FileName": "Dim_Account.csv",
        "TableType": "Dimension",
        "LoadToPowerBI": "Yes",
        "PrimaryKey": "AccountNumber",
        "Description": "Chart of accounts used to classify financial statement accounts.",
        "RecommendedImportOrder": 3
    },
    {
        "TableName": "Mapping_Financial_Statements",
        "FileName": "Mapping_Financial_Statements.csv",
        "TableType": "Mapping",
        "LoadToPowerBI": "Yes",
        "PrimaryKey": "AccountNumber",
        "Description": "Mapping between accounts and financial statement lines.",
        "RecommendedImportOrder": 4
    },
    {
        "TableName": "Fact_Trial_Balance",
        "FileName": "Fact_Trial_Balance.csv",
        "TableType": "Fact",
        "LoadToPowerBI": "Yes",
        "PrimaryKey": "CompanyID + PeriodID + AccountNumber",
        "Description": "Balanced trial balance by company, period, and account.",
        "RecommendedImportOrder": 5
    },
    {
        "TableName": "Fact_Income_Statement",
        "FileName": "Fact_Income_Statement.csv",
        "TableType": "Fact",
        "LoadToPowerBI": "Yes",
        "PrimaryKey": "CompanyID + PeriodID + RatioInput + LineOrder",
        "Description": "Income statement base and calculated lines.",
        "RecommendedImportOrder": 6
    },
    {
        "TableName": "Fact_Balance_Sheet",
        "FileName": "Fact_Balance_Sheet.csv",
        "TableType": "Fact",
        "LoadToPowerBI": "Yes",
        "PrimaryKey": "CompanyID + PeriodID + RatioInput + LineOrder",
        "Description": "Balance sheet base and calculated lines.",
        "RecommendedImportOrder": 7
    },
    {
        "TableName": "Fact_Cash_Flow",
        "FileName": "Fact_Cash_Flow.csv",
        "TableType": "Fact",
        "LoadToPowerBI": "Yes",
        "PrimaryKey": "CompanyID + PeriodID + RatioInput + LineOrder",
        "Description": "Cash flow statement and calculated cash flow indicators.",
        "RecommendedImportOrder": 8
    },
    {
        "TableName": "Fact_Market_Data",
        "FileName": "Fact_Market_Data.csv",
        "TableType": "Fact",
        "LoadToPowerBI": "Yes",
        "PrimaryKey": "CompanyID + PeriodID",
        "Description": "Synthetic market data including share price, market cap, enterprise value, EPS, and valuation inputs.",
        "RecommendedImportOrder": 9
    },
    {
        "TableName": "Fact_Employees",
        "FileName": "Fact_Employees.csv",
        "TableType": "Fact",
        "LoadToPowerBI": "Yes",
        "PrimaryKey": "CompanyID + PeriodID",
        "Description": "Synthetic employee counts used for revenue per employee and operating productivity metrics.",
        "RecommendedImportOrder": 10
    },
    {
        "TableName": "Fact_Liquidity_Ratios",
        "FileName": "Fact_Liquidity_Ratios.csv",
        "TableType": "Fact",
        "LoadToPowerBI": "Yes",
        "PrimaryKey": "CompanyID + PeriodID + RatioName",
        "Description": "Liquidity ratio outputs.",
        "RecommendedImportOrder": 11
    },
    {
        "TableName": "Fact_Profitability_Ratios",
        "FileName": "Fact_Profitability_Ratios.csv",
        "TableType": "Fact",
        "LoadToPowerBI": "Yes",
        "PrimaryKey": "CompanyID + PeriodID + RatioName",
        "Description": "Profitability ratio outputs.",
        "RecommendedImportOrder": 12
    },
    {
        "TableName": "Fact_Debt_Ratios",
        "FileName": "Fact_Debt_Ratios.csv",
        "TableType": "Fact",
        "LoadToPowerBI": "Yes",
        "PrimaryKey": "CompanyID + PeriodID + RatioName",
        "Description": "Debt and solvency ratio outputs.",
        "RecommendedImportOrder": 13
    },
    {
        "TableName": "Fact_Operating_Performance_Ratios",
        "FileName": "Fact_Operating_Performance_Ratios.csv",
        "TableType": "Fact",
        "LoadToPowerBI": "Yes",
        "PrimaryKey": "CompanyID + PeriodID + RatioName",
        "Description": "Operating performance and productivity ratio outputs.",
        "RecommendedImportOrder": 14
    },
    {
        "TableName": "Fact_Cash_Flow_Ratios",
        "FileName": "Fact_Cash_Flow_Ratios.csv",
        "TableType": "Fact",
        "LoadToPowerBI": "Yes",
        "PrimaryKey": "CompanyID + PeriodID + RatioName",
        "Description": "Cash flow indicator ratio outputs.",
        "RecommendedImportOrder": 15
    },
    {
        "TableName": "Fact_Investment_Valuation_Ratios",
        "FileName": "Fact_Investment_Valuation_Ratios.csv",
        "TableType": "Fact",
        "LoadToPowerBI": "Yes",
        "PrimaryKey": "CompanyID + PeriodID + RatioName",
        "Description": "Investment valuation ratio outputs.",
        "RecommendedImportOrder": 16
    },
    {
        "TableName": "Data_Quality_Issues",
        "FileName": "Data_Quality_Issues.csv",
        "TableType": "Audit",
        "LoadToPowerBI": "Optional",
        "PrimaryKey": "IssueType + DatasetName + ColumnName",
        "Description": "Summary of detected data quality issues.",
        "RecommendedImportOrder": 17
    },
    {
        "TableName": "Data_Quality_Issues_Detail",
        "FileName": "Data_Quality_Issues_Detail.csv",
        "TableType": "Audit",
        "LoadToPowerBI": "Optional",
        "PrimaryKey": "IssueType + DatasetName + RowIdentifier",
        "Description": "Detailed row-level data quality issue report.",
        "RecommendedImportOrder": 18
    }
]

powerbi_model_tables = pd.DataFrame(model_tables)

# ------------------------------------------------------------
# Step 3 - Define Power BI relationships
# ------------------------------------------------------------

relationships = []

fact_tables = [
    "Fact_Trial_Balance",
    "Fact_Income_Statement",
    "Fact_Balance_Sheet",
    "Fact_Cash_Flow",
    "Fact_Market_Data",
    "Fact_Employees",
    "Fact_Liquidity_Ratios",
    "Fact_Profitability_Ratios",
    "Fact_Debt_Ratios",
    "Fact_Operating_Performance_Ratios",
    "Fact_Cash_Flow_Ratios",
    "Fact_Investment_Valuation_Ratios"
]

for fact_table in fact_tables:
    relationships.append({
        "FromTable": "Dim_Company",
        "FromColumn": "CompanyID",
        "ToTable": fact_table,
        "ToColumn": "CompanyID",
        "Cardinality": "One-to-Many",
        "CrossFilterDirection": "Single",
        "IsActive": "Yes",
        "Description": f"Dim_Company filters {fact_table} by CompanyID."
    })

    relationships.append({
        "FromTable": "Dim_Period",
        "FromColumn": "PeriodID",
        "ToTable": fact_table,
        "ToColumn": "PeriodID",
        "Cardinality": "One-to-Many",
        "CrossFilterDirection": "Single",
        "IsActive": "Yes",
        "Description": f"Dim_Period filters {fact_table} by PeriodID."
    })

relationships.append({
    "FromTable": "Dim_Account",
    "FromColumn": "AccountNumber",
    "ToTable": "Fact_Trial_Balance",
    "ToColumn": "AccountNumber",
    "Cardinality": "One-to-Many",
    "CrossFilterDirection": "Single",
    "IsActive": "Yes",
    "Description": "Dim_Account filters Fact_Trial_Balance by AccountNumber."
})

relationships.append({
    "FromTable": "Dim_Account",
    "FromColumn": "AccountNumber",
    "ToTable": "Mapping_Financial_Statements",
    "ToColumn": "AccountNumber",
    "Cardinality": "One-to-One or One-to-Many",
    "CrossFilterDirection": "Single",
    "IsActive": "Yes",
    "Description": "Dim_Account connects to the statement mapping table."
})

powerbi_relationships = pd.DataFrame(relationships)

# ------------------------------------------------------------
# Step 4 - Define DAX measures
# ------------------------------------------------------------

dax_measures = r"""
Financial Ratios Analysis in BI
Topic 18 - Power BI DAX Measures
============================================================

Recommended base measures:

Total Debit =
SUM ( Fact_Trial_Balance[Debit] )

Total Credit =
SUM ( Fact_Trial_Balance[Credit] )

Trial Balance Difference =
[Total Debit] - [Total Credit]

Total Revenue =
CALCULATE (
    SUM ( Fact_Income_Statement[Amount] ),
    Fact_Income_Statement[RatioInput] = "Revenue"
)

Net Income =
CALCULATE (
    SUM ( Fact_Income_Statement[Amount] ),
    Fact_Income_Statement[RatioInput] = "Net Income"
)

Gross Profit =
CALCULATE (
    SUM ( Fact_Income_Statement[Amount] ),
    Fact_Income_Statement[RatioInput] = "Gross Profit"
)

Operating Income =
CALCULATE (
    SUM ( Fact_Income_Statement[Amount] ),
    Fact_Income_Statement[RatioInput] = "Operating Income"
)

Total Assets =
CALCULATE (
    SUM ( Fact_Balance_Sheet[Amount] ),
    Fact_Balance_Sheet[RatioInput] = "Total Assets"
)

Total Liabilities =
CALCULATE (
    SUM ( Fact_Balance_Sheet[Amount] ),
    Fact_Balance_Sheet[RatioInput] = "Total Liabilities"
)

Total Equity =
CALCULATE (
    SUM ( Fact_Balance_Sheet[Amount] ),
    Fact_Balance_Sheet[RatioInput] = "Total Equity"
)

Working Capital =
CALCULATE (
    SUM ( Fact_Balance_Sheet[Amount] ),
    Fact_Balance_Sheet[RatioInput] = "Working Capital"
)

Operating Cash Flow =
CALCULATE (
    SUM ( Fact_Cash_Flow[Amount] ),
    Fact_Cash_Flow[RatioInput] = "Operating Cash Flow"
)

Free Cash Flow =
CALCULATE (
    SUM ( Fact_Cash_Flow[Amount] ),
    Fact_Cash_Flow[RatioInput] = "Free Cash Flow"
)

Market Capitalization =
SUM ( Fact_Market_Data[MarketCapitalization] )

Enterprise Value =
SUM ( Fact_Market_Data[EnterpriseValue] )

Average Share Price =
AVERAGE ( Fact_Market_Data[SharePrice] )

Current Ratio =
CALCULATE (
    AVERAGE ( Fact_Liquidity_Ratios[RatioValue] ),
    Fact_Liquidity_Ratios[RatioName] = "Current Ratio"
)

Quick Ratio =
CALCULATE (
    AVERAGE ( Fact_Liquidity_Ratios[RatioValue] ),
    Fact_Liquidity_Ratios[RatioName] = "Quick Ratio"
)

Cash Ratio =
CALCULATE (
    AVERAGE ( Fact_Liquidity_Ratios[RatioValue] ),
    Fact_Liquidity_Ratios[RatioName] = "Cash Ratio"
)

Gross Margin =
CALCULATE (
    AVERAGE ( Fact_Profitability_Ratios[RatioValue] ),
    Fact_Profitability_Ratios[RatioName] = "Gross Margin"
)

Net Profit Margin =
CALCULATE (
    AVERAGE ( Fact_Profitability_Ratios[RatioValue] ),
    Fact_Profitability_Ratios[RatioName] = "Net Profit Margin"
)

Return on Assets =
CALCULATE (
    AVERAGE ( Fact_Profitability_Ratios[RatioValue] ),
    Fact_Profitability_Ratios[RatioName] = "Return on Assets"
)

Return on Equity =
CALCULATE (
    AVERAGE ( Fact_Profitability_Ratios[RatioValue] ),
    Fact_Profitability_Ratios[RatioName] = "Return on Equity"
)

Debt Ratio =
CALCULATE (
    AVERAGE ( Fact_Debt_Ratios[RatioValue] ),
    Fact_Debt_Ratios[RatioName] = "Debt Ratio"
)

Debt-to-Equity Ratio =
CALCULATE (
    AVERAGE ( Fact_Debt_Ratios[RatioValue] ),
    Fact_Debt_Ratios[RatioName] = "Debt-to-Equity Ratio"
)

Interest Coverage Ratio =
CALCULATE (
    AVERAGE ( Fact_Debt_Ratios[RatioValue] ),
    Fact_Debt_Ratios[RatioName] = "Interest Coverage Ratio"
)

Revenue per Employee =
CALCULATE (
    AVERAGE ( Fact_Operating_Performance_Ratios[RatioValue] ),
    Fact_Operating_Performance_Ratios[RatioName] = "Revenue per Employee"
)

Fixed Asset Turnover =
CALCULATE (
    AVERAGE ( Fact_Operating_Performance_Ratios[RatioValue] ),
    Fact_Operating_Performance_Ratios[RatioName] = "Fixed Asset Turnover"
)

Operating Cash Flow to Sales =
CALCULATE (
    AVERAGE ( Fact_Cash_Flow_Ratios[RatioValue] ),
    Fact_Cash_Flow_Ratios[RatioName] = "Operating Cash Flow to Sales"
)

Free Cash Flow Margin =
CALCULATE (
    AVERAGE ( Fact_Cash_Flow_Ratios[RatioValue] ),
    Fact_Cash_Flow_Ratios[RatioName] = "Free Cash Flow Margin"
)

Price / Earnings Ratio =
CALCULATE (
    AVERAGE ( Fact_Investment_Valuation_Ratios[RatioValue] ),
    Fact_Investment_Valuation_Ratios[RatioName] = "Price / Earnings Ratio"
)

Price / Book Ratio =
CALCULATE (
    AVERAGE ( Fact_Investment_Valuation_Ratios[RatioValue] ),
    Fact_Investment_Valuation_Ratios[RatioName] = "Price / Book Ratio"
)

Dividend Yield =
CALCULATE (
    AVERAGE ( Fact_Investment_Valuation_Ratios[RatioValue] ),
    Fact_Investment_Valuation_Ratios[RatioName] = "Dividend Yield"
)

Financial Health Score =
VAR LiquidityScore =
    IF ( [Current Ratio] >= 1.5, 20, IF ( [Current Ratio] >= 1.0, 12, 5 ) )
VAR ProfitabilityScore =
    IF ( [Net Profit Margin] >= 0.15, 20, IF ( [Net Profit Margin] >= 0.05, 12, 5 ) )
VAR DebtScore =
    IF ( [Debt Ratio] <= 0.40, 20, IF ( [Debt Ratio] <= 0.65, 12, 5 ) )
VAR CashFlowScore =
    IF ( [Operating Cash Flow to Sales] >= 0.15, 20, IF ( [Operating Cash Flow to Sales] >= 0.05, 12, 5 ) )
VAR ValuationScore =
    IF ( [Price / Earnings Ratio] <= 20, 20, IF ( [Price / Earnings Ratio] <= 35, 12, 5 ) )
RETURN
    LiquidityScore
    + ProfitabilityScore
    + DebtScore
    + CashFlowScore
    + ValuationScore
"""

# ------------------------------------------------------------
# Step 5 - Define Power BI import checklist
# ------------------------------------------------------------

checklist_data = [
    {
        "StepNumber": 1,
        "ChecklistArea": "Import",
        "Task": "Import Dim_Company, Dim_Period, and Dim_Account first.",
        "Status": "Pending"
    },
    {
        "StepNumber": 2,
        "ChecklistArea": "Import",
        "Task": "Import fact tables after dimension tables.",
        "Status": "Pending"
    },
    {
        "StepNumber": 3,
        "ChecklistArea": "Relationships",
        "Task": "Create one-to-many relationships from dimensions to fact tables.",
        "Status": "Pending"
    },
    {
        "StepNumber": 4,
        "ChecklistArea": "Relationships",
        "Task": "Use single-direction filtering from dimensions to facts.",
        "Status": "Pending"
    },
    {
        "StepNumber": 5,
        "ChecklistArea": "Data Types",
        "Task": "Set dates as Date, IDs as Whole Number/Text, and amounts as Decimal Number.",
        "Status": "Pending"
    },
    {
        "StepNumber": 6,
        "ChecklistArea": "Formatting",
        "Task": "Format currency, percentages, and decimal ratios properly.",
        "Status": "Pending"
    },
    {
        "StepNumber": 7,
        "ChecklistArea": "Measures",
        "Task": "Create DAX measures from PowerBI_DAX_Measures.txt.",
        "Status": "Pending"
    },
    {
        "StepNumber": 8,
        "ChecklistArea": "Validation",
        "Task": "Validate trial balance difference equals zero.",
        "Status": "Pending"
    },
    {
        "StepNumber": 9,
        "ChecklistArea": "Validation",
        "Task": "Validate balance sheet equation: Assets = Liabilities + Equity.",
        "Status": "Pending"
    },
    {
        "StepNumber": 10,
        "ChecklistArea": "Dashboard",
        "Task": "Build overview, ratio trend, company comparison, and data quality pages.",
        "Status": "Pending"
    }
]

powerbi_checklist = pd.DataFrame(checklist_data)

# ------------------------------------------------------------
# Step 6 - Validate files and create model validation report
# ------------------------------------------------------------

validation_records = []

for _, row in powerbi_model_tables.iterrows():

    file_name = row["FileName"]
    file_path = base_path / file_name

    if file_path.exists():
        try:
            df = pd.read_csv(file_path)
            row_count = len(df)
            column_count = len(df.columns)
            status = "FOUND"
            message = "File is ready for Power BI import."
        except Exception as error:
            row_count = 0
            column_count = 0
            status = "ERROR"
            message = str(error)
    else:
        row_count = 0
        column_count = 0
        status = "MISSING"
        message = "File was not found. Run the required previous topic."

    validation_records.append({
        "TableName": row["TableName"],
        "FileName": file_name,
        "TableType": row["TableType"],
        "LoadToPowerBI": row["LoadToPowerBI"],
        "FileStatus": status,
        "RowCount": row_count,
        "ColumnCount": column_count,
        "ValidationMessage": message
    })

powerbi_validation = pd.DataFrame(validation_records)

# ------------------------------------------------------------
# Step 7 - Export Power BI preparation files
# ------------------------------------------------------------

powerbi_model_tables.to_csv(
    base_path / "PowerBI_Model_Tables.csv",
    index=False
)

powerbi_relationships.to_csv(
    base_path / "PowerBI_Relationships.csv",
    index=False
)

powerbi_checklist.to_csv(
    base_path / "PowerBI_Import_Checklist.csv",
    index=False
)

powerbi_validation.to_csv(
    base_path / "PowerBI_Model_Validation.csv",
    index=False
)

(base_path / "PowerBI_DAX_Measures.txt").write_text(
    dax_measures,
    encoding="utf-8"
)

print("PowerBI_Model_Tables.csv created successfully.")
print("PowerBI_Relationships.csv created successfully.")
print("PowerBI_Import_Checklist.csv created successfully.")
print("PowerBI_Model_Validation.csv created successfully.")
print("PowerBI_DAX_Measures.txt created successfully.")

# ------------------------------------------------------------
# Step 8 - Print validation summary
# ------------------------------------------------------------

print()
print("==============================")
print("POWER BI MODEL SUMMARY")
print("==============================")

print("Model tables:", len(powerbi_model_tables))
print("Relationships:", len(powerbi_relationships))
print("Checklist tasks:", len(powerbi_checklist))

print()
print("Tables by type:")
print(
    powerbi_model_tables
    .groupby("TableType")["TableName"]
    .count()
    .reset_index(name="NumberOfTables")
)

print()
print("Power BI file validation:")
print(
    powerbi_validation[
        [
            "TableName",
            "FileName",
            "FileStatus",
            "RowCount",
            "ColumnCount"
        ]
    ]
)

print()
print("Missing files:")
missing_files = powerbi_validation[powerbi_validation["FileStatus"] == "MISSING"]

if missing_files.empty:
    print("No missing files. Power BI model files are ready.")
else:
    print(missing_files[["TableName", "FileName"]])

print()
print("Files created:")

created_files = [
    "PowerBI_Model_Tables.csv",
    "PowerBI_Relationships.csv",
    "PowerBI_Import_Checklist.csv",
    "PowerBI_Model_Validation.csv",
    "PowerBI_DAX_Measures.txt"
]

for file_name in created_files:
    if (base_path / file_name).exists():
        print("-", file_name)

print()
print("==============================")
print("NEXT STEPS")
print("==============================")
print("1. Open Power BI Desktop.")
print("2. Import the CSV files listed in PowerBI_Model_Tables.csv.")
print("3. Create relationships using PowerBI_Relationships.csv.")
print("4. Add DAX measures from PowerBI_DAX_Measures.txt.")
print("5. Validate row counts using PowerBI_Model_Validation.csv.")
print("6. Build dashboard pages for financial overview, ratios, trends, and data quality.")
print()
print("Topic 18 completed successfully.")

financial_ratios_bi_training/PowerBI_Model_Design.md

Validation Checklist

• Checkpoint 1: Validate source fields, row counts, relationship keys, financial reasonableness, and business interpretation before moving to the next topic.
• Checkpoint 2: Validate source fields, row counts, relationship keys, financial reasonableness, and business interpretation before moving to the next topic.
• Checkpoint 3: Validate source fields, row counts, relationship keys, financial reasonableness, and business interpretation before moving to the next topic.
• Checkpoint 4: Validate source fields, row counts, relationship keys, financial reasonableness, and business interpretation before moving to the next topic.
• Checkpoint 5: Validate source fields, row counts, relationship keys, financial reasonableness, and business interpretation before moving to the next topic.
• Checkpoint 6: Validate source fields, row counts, relationship keys, financial reasonableness, and business interpretation before moving to the next topic.
• Checkpoint 7: Validate source fields, row counts, relationship keys, financial reasonableness, and business interpretation before moving to the next topic.
• Checkpoint 8: Validate source fields, row counts, relationship keys, financial reasonableness, and business interpretation before moving to the next topic.

Final Recommendation

Before moving forward, open the generated file, review the columns, confirm the number of companies and periods, and make sure the numbers make financial sense. Good BI starts with clean, explainable, and validated data.