Praveen Kumar

M&A Deal Tracker Dashboard Complete Build Guide — DAX Measures, Dashboard Layout & Portfolio Notes Praveen Kumar Nutakki • EY BI / Visualisation Application Project • March 2026 1. DATA MODEL & TABLE RELATIONSHIPS Star Schema — 6 Tables dim_date 1,186 rows — Jan 2023 to Mar 2026. date_id is the join key (YYYYMMDD integer). dim_sector 5 rows — Technology, Healthcare, Financial Services, Industrial, Consumer. Includes EV/EBITDA benchmark per sector. dim_stage 6 rows — Sourcing → Initial Review → Due Diligence → Negotiation → Signing → Closed. Includes typical_days benchmark. dim_deal 25 rows — One row per deal. Contains deal value, EBITDA, EV/EBITDA multiple, advisory fee, current stage, geography. fact_deal_activity 92 rows — One row per deal per stage traversed. Contains entry_date_id, days_in_stage, status_note, is_current_stage flag. fact_fees_monthly 155 rows — Monthly fee accrual per deal for revenue tracking and pipeline fee forecasting. Relationships to set in Power BI • dim_deal[deal_id] → fact_deal_activity[deal_id] (one-to-many) • dim_deal[deal_id] → fact_fees_monthly[deal_id] (one-to-many) • dim_stage[stage_id] → fact_deal_activity[stage_id] (one-to-many) • dim_sector[sector_id] → fact_deal_activity[sector_id] (one-to-many) • dim_sector[sector_id] → dim_deal[sector_id] (one-to-many) • dim_date[date_id] → fact_deal_activity[entry_date_id] (one-to-many) 3 Planted Anomalies to Find • DEAL-007 (NovaBridge Capital) — EV/EBITDA at 31.4x against a 14.5x sector benchmark. Overvalued by 116%. • DEAL-014 (Ironforge Metals) — Advisory fee at 7.1% of deal value against a 1.5–3.0% sector norm. Fee anomaly flag. • DEAL-003, DEAL-009, DEAL-019 — All stalled in Due Diligence at 3x typical days. Velocity flag: CRITICAL STALL. 2. DAX MEASURES — COMPLETE LIBRARY Pipeline KPI Measures Total Pipeline Value Total Pipeline Value = SUMX( FILTER(dim_deal, dim_deal[deal_status] <> "Closed"), dim_deal[deal_value_usd_m] ) Total Deals Active Total Deals Active = COUNTROWS(FILTER(dim_deal, dim_deal[deal_status] = "Active")) Total Deals Closed Total Deals Closed = COUNTROWS(FILTER(dim_deal, dim_deal[deal_status] = "Closed")) Total Advisory Fees Total Advisory Fees = SUM(dim_deal[advisory_fee_usd_m]) Average Deal Value Avg Deal Value = AVERAGEX(dim_deal, dim_deal[deal_value_usd_m]) Avg EV/EBITDA Multiple Avg EV EBITDA Multiple = AVERAGEX(dim_deal, dim_deal[ev_ebitda_multiple]) Stage & Velocity Measures Deals in Due Diligence Deals in Due Diligence = CALCULATE( COUNTROWS(fact_deal_activity), fact_deal_activity[is_current_stage] = 1, dim_stage[stage_name] = "Due Diligence" ) Avg Days in Current Stage Avg Days in Stage = CALCULATE( AVERAGE(fact_deal_activity[days_in_stage]), fact_deal_activity[is_current_stage] = 1 ) Stalled Deals Count Stalled Deals = COUNTROWS( FILTER( fact_deal_activity, fact_deal_activity[status_note] = "STALLED" && fact_deal_activity[is_current_stage] = 1 ) ) Stage Velocity % vs Benchmark Velocity vs Benchmark % = DIVIDE( CALCULATE(AVERAGE(fact_deal_activity[days_in_stage]), fact_deal_activity[is_current_stage] = 1), AVERAGE(dim_stage[typical_days]), 0 ) * 100 Valuation Measures Premium to Sector Benchmark Avg Premium to Benchmark = AVERAGEX( dim_deal, dim_deal[ev_ebitda_multiple] - RELATED(dim_sector[ev_ebitda_benchmark]) ) Overvalued Deals Count Overvalued Deals = COUNTROWS( FILTER( dim_deal, dim_deal[ev_ebitda_multiple] > RELATED(dim_sector[ev_ebitda_benchmark]) * 1.40 ) ) Fee Measures Avg Fee % of Deal Value Avg Fee Pct = AVERAGEX( dim_deal, DIVIDE(dim_deal[advisory_fee_usd_m], dim_deal[deal_value_usd_m], 0) * 100 ) Fee Anomaly Flag Fee Anomaly Count = COUNTROWS( FILTER( dim_deal, DIVIDE(dim_deal[advisory_fee_usd_m], dim_deal[deal_value_usd_m], 0) * 100 > 4.5 ) ) Monthly Fees Accrued (for trend chart) Monthly Fees = CALCULATE( SUM(fact_fees_monthly[monthly_fee_usd_m]), USERELATIONSHIP(dim_date[fiscal_period], fact_fees_monthly[fiscal_period]) ) 3. POWER BI DASHBOARD LAYOUT — 3 PAGES Page 1 — Pipeline Overview Theme: Show the CFO the full deal pipeline at a glance — value, count, sector mix, and stage distribution in 30 seconds. • KPI Card — Total Pipeline Value (USD M) • KPI Card — Total Deals Active • KPI Card — Total Advisory Fees (USD M) • KPI Card — Deals Closed • Funnel chart — Deal count by stage (Sourcing → Closed) [use clustered bar if funnel not available] • Donut chart — Pipeline value % by sector • Bar chart — Total deal value by geography • Table — All 25 deals with deal_id, target, sector, stage, value, status — conditional formatting: Active = blue, Closed = green, Pipeline = grey • Slicer — Sector name • Slicer — Geography • Slicer — Deal status Page 2 — Deal Intelligence (Stage Velocity) Theme: Identify which deals are stalling and where. The deal team uses this page every Monday morning. • KPI Card — Avg Days in Current Stage • KPI Card — Stalled Deals count (red if > 0) • KPI Card — Velocity vs Benchmark % • KPI Card — Deals in Due Diligence • Clustered bar — Avg days in stage vs benchmark days (side by side per stage) • Scatter plot — X axis: deal value | Y axis: days in current stage | Color: stage name | Size: advisory fee • Table — Stalled deals detail: deal_id, target, stage, days_in_stage, benchmark, pct_over, recommended_action • Conditional formatting on table — Red for CRITICAL STALL, Amber for WARNING, Green for ON TRACK • Slicer — Stage name • Slicer — Sector Page 3 — Valuation & Fee Intelligence Theme: Flag overvalued targets and fee anomalies. This is the anomaly detection page — it demonstrates the same analytical rigour as the Meridian anomaly system. • KPI Card — Avg EV/EBITDA Multiple across all deals • KPI Card — Overvalued Deals count (red if > 0) • KPI Card — Avg Fee % of Deal Value • KPI Card — Fee Anomaly Count (red if > 0) • Bar chart — EV/EBITDA multiple by deal with benchmark reference line per sector [use constant line = sector avg] • Scatter plot — X axis: deal value | Y axis: EV/EBITDA multiple | Color: sector | Reference lines at sector benchmarks • Bar chart — Advisory fee % of deal value per deal | Reference line at 3.0% | DEAL-014 will spike visually • Line chart — Monthly fee accrual trend Jan 2023 – Mar 2026 • Table — All deals: target, sector, EV/EBITDA, benchmark, premium %, fee %, valuation_flag • Conditional formatting — Red for OVERVALUED and FEE ANOMALY, Green for FAIRLY VALUED • Slicer — Sector • Slicer — Deal type 4. PORTFOLIO WRITE-UP — WHAT TO SAY GitHub README — Key Points to Cover • What problem this solves: M&A deal teams at advisory firms like EY manage 20–50 live transactions simultaneously. Without a centralised tracker, deals stall, fees get miscalculated, and overvalued targets slip through to signing. • What you built: A complete Power BI intelligence platform covering pipeline monitoring, stage velocity tracking, valuation benchmarking, and automated fee anomaly detection. • Key findings from the data: 3 deals stalled in Due Diligence at 3x typical duration. 1 target (NovaBridge Capital) valued at 31.4x EV/EBITDA against a 14.5x sector benchmark — 116% premium. 1 advisory fee at 7.1% of deal value vs 1.5–3.0% sector norm. • Tech stack: Python (Pandas, NumPy), SQL Server, Power BI, DAX, Power Query, Git. Framer Portfolio Description Add as your second project below Meridian. Title: M&A Deal Tracker — Transaction Advisory Intelligence Platform. Results to show: 25 deals tracked • 3 anomalies detected • 3 dashboard pages • 15+ DAX measures • 4 SQL queries. LinkedIn Post Hook Most M&A deals don’t fail at the negotiation table. They fail in Due Diligence — when nobody noticed the deal had been sitting in the same stage for 90 days. I built a dashboard that would have caught it on day 10. Praveen Kumar Nutakki • praveenkumar66.framer.website • github.com/praveennuthakki666