Power BI Financial Dashboard

6.1 Why Power BI for Financial Dashboards?

In Chapters 3–5, you built Python pipelines that compute value drivers, ratios, and ROIC. These pipelines are powerful — but their output is DataFrames and static charts. When you present to a CFO, a board, or an investment committee, you need something more: an interactive, self-service dashboard where stakeholders can explore the data themselves, filter by year or segment, and drill into the numbers behind the headline.

Microsoft Power BI is the tool of choice for this task for several reasons:

6.3 Preparing Your Financial Data for Power BI

Power BI works best with clean, structured, unpivoted data. The financial DataFrames we generated in Chapters 3–5 are wide-format (years as columns, metrics as rows). Power BI prefers long/tall format (one row per metric per year). Let us prepare the data correctly.

6.3.1 The Target Schema

Your Power BI data model should have this structure for a financial dashboard:

6.3.2 Python Script: Generate Power BI-Ready Financial Data

Run this Python script to fetch financial data for multiple Indian companies and export it in Power BI-friendly format. You must run this script and save the CSV files before you start the Power BI section.

"""
Generate Power BI-ready financial datasets for Indian companies.
Run this script once. It produces two CSV files:
  - financial_data.csv  →  Raw financial statement line items (long format)
  - ratio_data.csv      →  Pre-computed financial ratios (long format)

Import both into Power BI Desktop as your data source.
"""

import yfinance as yf
import pandas as pd
import numpy as np
import time

# ============================================================
# CONFIGURATION: Choose your companies
# ============================================================
COMPANIES = {
    'TCS.NS':           {'Name': 'Tata Consultancy Services', 'Sector': 'IT Services'},
    'INFY.NS':          {'Name': 'Infosys', 'Sector': 'IT Services'},
    'WIPRO.NS':         {'Name': 'Wipro', 'Sector': 'IT Services'},
    'HCLTECH.NS':       {'Name': 'HCL Technologies', 'Sector': 'IT Services'},
    'TECHM.NS':         {'Name': 'Tech Mahindra', 'Sector': 'IT Services'},
}

# Alternatively, for a cross-sector comparison:
# COMPANIES = {
#     'TCS.NS':           {'Name': 'TCS', 'Sector': 'IT'},
#     'ASIANPAINT.NS':    {'Name': 'Asian Paints', 'Sector': 'Consumer'},
#     'RELIANCE.NS':      {'Name': 'Reliance Industries', 'Sector': 'Conglomerate'},
#     'HDFCBANK.NS':      {'Name': 'HDFC Bank', 'Sector': 'Banking'},
#     'SUNPHARMA.NS':     {'Name': 'Sun Pharma', 'Sector': 'Pharma'},
# }

# ============================================================
# DATA EXTRACTION
# ============================================================

def safe_get(df, candidates):
    """Try multiple field names, return first match."""
    if isinstance(candidates, str):
        candidates = [candidates]
    for c in candidates:
        if c in df.index:
            return df.loc[c]
    return None

financial_rows = []
ratio_rows = []

for ticker, meta in COMPANIES.items():
    print(f"Fetching {meta['Name']} ({ticker})...")
    try:
        t = yf.Ticker(ticker)
        info = t.info
        is_df = t.financials
        bs_df = t.balance_sheet
        cf_df = t.cashflow

        # --- Raw Financial Metrics (long format) ---
        mappings = {
            'Total Revenue': ['Total Revenue', 'Revenue'],
            'Cost of Revenue': ['Cost Of Goods Sold', 'Cost of Revenue'],
            'Gross Profit': ['Gross Profit'],
            'Operating Income': ['Operating Income', 'EBIT'],
            'EBITDA': ['EBITDA'],
            'Net Income': ['Net Income', 'Net Income Common Stockholders'],
            'Total Assets': ['Total Assets'],
            'Current Assets': ['Current Assets', 'Total Current Assets'],
            'Current Liabilities': ['Current Liabilities', 'Total Current Liabilities'],
            'Total Debt': ['Total Debt', 'Long Term Debt'],
            'Shareholders Equity': ['Total Equity Gross Minority Interest',
                                     'Stockholders Equity', 'Total Equity'],
            'Cash and Equivalents': ['Cash And Cash Equivalents',
                                      'Cash Cash Equivalents And Short Term Investments'],
            'Operating Cash Flow': ['Operating Cash Flow'],
            'Capital Expenditure': ['Capital Expenditure', 'Capital Expenditure Reported'],
            'Accounts Receivable': ['Accounts Receivable', 'Net Receivables'],
            'Inventory': ['Inventory', 'Inventories'],
        }

        for metric_name, field_candidates in mappings.items():
            val = safe_get(is_df, field_candidates)
            if val is None:
                val = safe_get(bs_df, field_candidates)
            if val is None:
                val = safe_get(cf_df, field_candidates)
            if val is not None:
                for fiscal_year, value in val.items():
                    if pd.notna(value) and value != 0:
                        financial_rows.append({
                            'Company': meta['Name'],
                            'Ticker': ticker.replace('.NS', ''),
                            'Sector': meta['Sector'],
                            'Year': int(str(fiscal_year).split('-')[0]),
                            'Metric': metric_name,
                            'Value': round(value / 1e7, 2)  # Convert to Rs. Crore
                        })

        # --- Pre-computed Ratios (long format) ---
        rev  = safe_get(is_df, ['Total Revenue', 'Revenue'])
        gp   = safe_get(is_df, ['Gross Profit'])
        ebit = safe_get(is_df, ['Operating Income', 'EBIT'])
        ni   = safe_get(is_df, ['Net Income', 'Net Income Common Stockholders'])
        ebitda = safe_get(is_df, ['EBITDA'])
        ca   = safe_get(bs_df, ['Current Assets', 'Total Current Assets'])
        cl   = safe_get(bs_df, ['Current Liabilities', 'Total Current Liabilities'])
        debt = safe_get(bs_df, ['Total Debt', 'Long Term Debt'])
        eq   = safe_get(bs_df, ['Total Equity Gross Minority Interest',
                                 'Stockholders Equity', 'Total Equity'])
        cash = safe_get(bs_df, ['Cash And Cash Equivalents',
                                 'Cash Cash Equivalents And Short Term Investments'])
        ta   = safe_get(bs_df, ['Total Assets'])
        ocf  = safe_get(cf_df, ['Operating Cash Flow'])
        capex = safe_get(cf_df, ['Capital Expenditure', 'Capital Expenditure Reported'])

        for year_idx in is_df.columns:
            fy = int(str(year_idx).split('-')[0])

            try:
                if rev is not None and rev.get(year_idx):
                    r = rev[year_idx]
                    # Margins
                    if gp is not None and gp.get(year_idx):
                        ratio_rows.append({'Company': meta['Name'], 'Ticker': ticker.replace('.NS',''),
                                           'Sector': meta['Sector'], 'Year': fy,
                                           'Ratio': 'Gross Margin (%)',
                                           'Value': round((gp[year_idx]/r)*100, 2)})
                    if ebit is not None and ebit.get(year_idx):
                        ratio_rows.append({'Company': meta['Name'], 'Ticker': ticker.replace('.NS',''),
                                           'Sector': meta['Sector'], 'Year': fy,
                                           'Ratio': 'Operating Margin (%)',
                                           'Value': round((ebit[year_idx]/r)*100, 2)})
                    if ni is not None and ni.get(year_idx):
                        ratio_rows.append({'Company': meta['Name'], 'Ticker': ticker.replace('.NS',''),
                                           'Sector': meta['Sector'], 'Year': fy,
                                           'Ratio': 'Net Margin (%)',
                                           'Value': round((ni[year_idx]/r)*100, 2)})
                    if ebitda is not None and ebitda.get(year_idx):
                        ratio_rows.append({'Company': meta['Name'], 'Ticker': ticker.replace('.NS',''),
                                           'Sector': meta['Sector'], 'Year': fy,
                                           'Ratio': 'EBITDA Margin (%)',
                                           'Value': round((ebitda[year_idx]/r)*100, 2)})
                    # Revenue Growth
                    prev_year = str(fy - 1)
                    for y2 in is_df.columns:
                        if str(y2).startswith(prev_year):
                            prev_rev = rev[y2]
                            if prev_rev and prev_rev != 0:
                                growth = ((r - prev_rev) / prev_rev) * 100
                                ratio_rows.append({'Company': meta['Name'], 'Ticker': ticker.replace('.NS',''),
                                                   'Sector': meta['Sector'], 'Year': fy,
                                                   'Ratio': 'Revenue Growth (%)',
                                                   'Value': round(growth, 2)})
                            break

                # Ratios from balance sheet
                if ca is not None and cl is not None and ca.get(year_idx) and cl.get(year_idx):
                    cr = (ca[year_idx] / cl[year_idx])
                    ratio_rows.append({'Company': meta['Name'], 'Ticker': ticker.replace('.NS',''),
                                       'Sector': meta['Sector'], 'Year': fy,
                                       'Ratio': 'Current Ratio', 'Value': round(cr, 2)})

                if debt is not None and eq is not None and debt.get(year_idx) and eq.get(year_idx):
                    de = debt[year_idx] / eq[year_idx]
                    ratio_rows.append({'Company': meta['Name'], 'Ticker': ticker.replace('.NS',''),
                                       'Sector': meta['Sector'], 'Year': fy,
                                       'Ratio': 'Debt-to-Equity', 'Value': round(de, 2)})

                # ROIC (preliminary)
                if ebit is not None and eq is not None and debt is not None and cash is not None:
                    if all(x.get(year_idx) for x in [ebit, eq, debt, cash]):
                        nopat = ebit[year_idx] * 0.75
                        ic = eq[year_idx] + debt[year_idx] - cash[year_idx]
                        if ic > 0:
                            roic = (nopat / ic) * 100
                            ratio_rows.append({'Company': meta['Name'], 'Ticker': ticker.replace('.NS',''),
                                               'Sector': meta['Sector'], 'Year': fy,
                                               'Ratio': 'ROIC (%)', 'Value': round(roic, 2)})

                # ROE
                if ni is not None and eq is not None and ni.get(year_idx) and eq.get(year_idx):
                    roe = (ni[year_idx] / eq[year_idx]) * 100
                    ratio_rows.append({'Company': meta['Name'], 'Ticker': ticker.replace('.NS',''),
                                       'Sector': meta['Sector'], 'Year': fy,
                                       'Ratio': 'ROE (%)', 'Value': round(roe, 2)})

                # Asset Turnover
                if rev is not None and ta is not None and rev.get(year_idx) and ta.get(year_idx):
                    at = rev[year_idx] / ta[year_idx]
                    ratio_rows.append({'Company': meta['Name'], 'Ticker': ticker.replace('.NS',''),
                                       'Sector': meta['Sector'], 'Year': fy,
                                       'Ratio': 'Asset Turnover', 'Value': round(at, 2)})

                # FCF Yield
                mkt_cap = info.get('marketCap')
                if ocf is not None and capex is not None and mkt_cap:
                    if ocf.get(year_idx) and capex.get(year_idx):
                        fcf = ocf[year_idx] + capex[year_idx]
                        fcf_yield = (fcf / mkt_cap) * 100
                        ratio_rows.append({'Company': meta['Name'], 'Ticker': ticker.replace('.NS',''),
                                           'Sector': meta['Sector'], 'Year': fy,
                                           'Ratio': 'FCF Yield (%)', 'Value': round(fcf_yield, 2)})
            except Exception:
                pass  # Skip individual year/ratio failures

        print(f"  ✓ {meta['Name']} — {len([r for r in financial_rows if r['Company']==meta['Name']])} financial rows, "
              f"{len([r for r in ratio_rows if r['Company']==meta['Name']])} ratio rows")

    except Exception as e:
        print(f"  ✗ {meta['Name']}: {e}")
    time.sleep(1.5)

# Save to CSV
df_fin = pd.DataFrame(financial_rows)
df_rat = pd.DataFrame(ratio_rows)

df_fin.to_csv('financial_data.csv', index=False)
df_rat.to_csv('ratio_data.csv', index=False)

print(f"\n=== DONE ===")
print(f"financial_data.csv: {len(df_fin)} rows, {df_fin['Company'].nunique()} companies, "
      f"{df_fin['Year'].min()}-{df_fin['Year'].max()}")
print(f"ratio_data.csv: {len(df_rat)} rows, {df_rat['Company'].nunique()} companies")
print(f"\nUnique Metrics: {sorted(df_fin['Metric'].unique())}")
print(f"Unique Ratios: {sorted(df_rat['Ratio'].unique())}")
print(f"\nImport both CSV files into Power BI Desktop to begin the dashboard build.")

6.4 Building the Data Model in Power BI

With your CSV files ready, let us build the data model — the foundation of your entire dashboard.

6.4.1 Import Data

6.4.2 Create the Date Table (Essential for Time Intelligence)

DAX time intelligence functions (YEAR-OVER-YEAR growth, running totals, moving averages) require a proper date table. Create one now:

-- 1. Go to Home → New Table (or Modeling → New Table)
-- 2. Paste this DAX expression:

DateTable =
VAR MinYear = MIN(ratio_data[Year])
VAR MaxYear = MAX(ratio_data[Year])
RETURN
ADDCOLUMNS(
    CALENDAR(DATE(MinYear, 4, 1), DATE(MaxYear, 3, 31)),
    "FiscalYear",
        IF(MONTH([Date]) >= 4,
           YEAR([Date]),
           YEAR([Date]) - 1
        ),
    "MonthNum", MONTH([Date]),
    "MonthName", FORMAT([Date], "MMM"),
    "Quarter", "Q" & FORMAT([Date], "Q"),
    "FiscalQuarter",
        "FY" & RIGHT(
            IF(MONTH([Date]) >= 4, YEAR([Date]), YEAR([Date]) - 1), 2
        ) & "-Q" & FORMAT(
            IF(MONTH([Date]) >= 4,
               INT((MONTH([Date]) - 4) / 3) + 1,
               INT((MONTH([Date]) + 8) / 3) + 1
            ), "0"
        )
)

-- 3. Mark as date table: Select DateTable → Table Tools → Mark as Date Table → choose [Date] column.

This creates a date table aligned to the Indian fiscal year (April–March). If your data uses a January–December calendar year, simplify by removing the FiscalYear logic and using the plain Year column.

6.4.3 Create Relationships

In Model view, drag the Year column from ratio_data to the FiscalYear column in DateTable. This creates a one-to-many relationship (one DateTable row → many ratio_data rows for that year).

6.5 Writing DAX Measures for Financial Analysis

DAX (Data Analysis Expressions) is the formula language of Power BI. Measures are DAX formulas that compute values dynamically based on the current filter context. When a user clicks on "TCS" in a slicer, all measures automatically recompute for TCS.

6.5.1 KPI Measures

Right-click the ratio_data table in the Data pane and select New Measure. Create each of these:

The pattern for each KPI measure is identical — only the ratio name changes. Here is the full DAX for one:

Latest ROIC =
VAR LatestYear = MAX(ratio_data[Year])
RETURN
CALCULATE(
    MAX(ratio_data[Value]),
    ratio_data[Ratio] = "ROIC (%)",
    ratio_data[Year] = LatestYear
)

6.5.2 Trend Measures

For line charts that show a metric over time, create this simple aggregation measure:

Operating Margin =
CALCULATE(
    MAX(ratio_data[Value]),
    ratio_data[Ratio] = "Operating Margin (%)"
)

Create similar measures for Gross Margin, Net Margin, ROIC, ROE, Revenue Growth, and Current Ratio. You will need one trend measure per ratio you want to chart.

6.5.3 Conditional Color Measure

For KPI cards that turn green or red based on performance:

ROIC Color =
VAR roic = [Latest ROIC]
RETURN
SWITCH(
    TRUE(),
    roic >= 25, "#00C9A7",    -- Green: Excellent
    roic >= 15, "#6C8CFF",    -- Blue: Good
    roic >= 10, "#F0A040",    -- Orange: Adequate
    roic < 10,  "#E0556A",    -- Red: Poor
    "#A0A0B8"                 -- Grey: No data
)

Operating Margin =
SUM(ratio_data[Value])

6.6 Building the Dashboard Visuals

Now we assemble the visuals. Switch to Report view. The canvas is where your dashboard comes to life.

6.6.1 Dashboard Layout: The Grid

An executive dashboard follows a Z-pattern reading flow: top-left (most important) → top-right → bottom-left → bottom-right. Arrange your canvas as follows:

6.6.2 Step-by-Step Visual Build

6.6.3 Cross-Highlighting and Interactivity

Power BI automatically enables cross-filtering and cross-highlighting between visuals. Verify these interactions:

• Click a bar in the ROIC bar chart → all other visuals filter to that company.
• Click a year on the trend chart → KPI cards stay at latest year (by design), but the trend chart highlights that point.
• Use the Company slicer → everything updates.

To customize interactions: select a visual → Format ribbon → Edit Interactions → choose which visuals it filters, highlights, or ignores.

6.7 Executive Dashboard Design Principles

A technically correct dashboard can still fail if it does not communicate effectively. Here are the design principles that separate a professional executive dashboard from an amateur chart collection:

6.7.1 The Five Principles

6.7.2 Color Palette for Financial Dashboards