Power BI for Marketing Analysts: The Dashboard Your CMO Opens Every Monday

The Scenario: B2B Marketing in Chicago, Too Many Dashboards

You’re the marketing data analyst at a mid-size B2B company based in Chicago. Your stack looks roughly like this:

• Ad platforms: Google Ads, LinkedIn Ads, Meta Ads
• Marketing automation: HubSpot or Marketo
• CRM: Dynamics 365 or Salesforce
• Web analytics: Google Analytics 4

You already have several Power BI reports:

• A paid media report from the ads team
• A leads and MQLs report from marketing ops
• A pipeline report from sales ops

The CMO hates this:

• No single view of spend → leads → opportunities → revenue
• Different numbers for “leads” depending on the report
• No way to see campaign ROI without opening three tools

The goal: one CMO Monday dashboard in Power BI that answers:

1. What did we spend last week and month-to-date, by channel and campaign?
2. What did we get: leads, MQLs, SQLs, opportunities, revenue?
3. Which campaigns are efficient, which are wasting budget?
4. Are we on track for this quarter’s pipeline target?

We’ll build that around three core tables and a few key measures.

Model First: The Minimum Tables You Actually Need

You can’t fix a CMO dashboard with visuals alone. You need a model that reflects the funnel.

For this scenario, keep it lean:

1. FactSpend – daily or campaign-level spend
2. FactLeads – individual leads with funnel stage
3. FactOpportunities – CRM opportunities with value and stage
4. DimDate – standard date table
5. DimChannel – Paid search, Paid social, Organic, Email, etc.
6. DimCampaign – Campaign metadata across platforms

Relationships (Import mode)

Assuming Import mode in Power BI Desktop and standard star schema:

• DimDate[Date] → many-to-one to FactSpend[SpendDate]
• DimDate[Date] → many-to-one to FactLeads[LeadDate]
• DimDate[Date] → many-to-one to FactOpportunities[CreatedDate]
• DimChannel[ChannelKey] → many-to-one to each fact table’s ChannelKey
• DimCampaign[CampaignKey] → many-to-one to each fact table’s CampaignKey

Use a single active relationship from DimDate to each fact, and rely on USERELATIONSHIP only when you truly need alternate date logic (e.g., opportunity close date vs created date).

Create DimDate in Power Query or DAX. For a CMO dashboard where you want flexible relative periods, a DAX calendar in a dedicated table works well:

DimDate =
ADDCOLUMNS (
    CALENDAR ( DATE ( YEAR ( TODAY() ) - 2, 1, 1 ), DATE ( YEAR ( TODAY() ) + 1, 12, 31 ) ),
    "Year", YEAR ( [Date] ),
    "MonthNumber", MONTH ( [Date] ),
    "MonthName", FORMAT ( [Date], "MMM" ),
    "YearMonth", FORMAT ( [Date], "YYYY-MM" ),
    "WeekStart", [Date] - WEEKDAY ( [Date], 2 ) + 1,
    "WeekEnd", [Date] - WEEKDAY ( [Date], 2 ) + 7,
    "IsCurrentWeek", IF ( [Date] >= STARTOFWEEK ( TODAY(), 2 ) && [Date] <= ENDOFWEEK ( TODAY(), 2 ), TRUE (), FALSE () ),
    "IsCurrentMonth", YEAR ( [Date] ) = YEAR ( TODAY() ) && MONTH ( [Date] ) = MONTH ( TODAY() ),
    "IsCurrentQuarter", YEAR ( [Date] ) = YEAR ( TODAY() ) && QUARTER ( [Date] ) = QUARTER ( TODAY() ),
    "IsCurrentYear", YEAR ( [Date] ) = YEAR ( TODAY() )
)

This gives you calendar intelligence without relying on Auto Date/Time (which is better disabled for anything beyond a toy model).

Measures That Actually Answer CMO Questions

The CMO doesn’t care about impressions or click-through rate in the Monday dashboard. They care about:

• Spend
• Leads and funnel conversion
• Opportunities and revenue
• Efficiency (cost per lead, cost per opportunity, ROI)

All measures below assume:

• FactSpend[SpendAmount] is numeric currency
• FactLeads[LeadId] is unique per lead
• FactLeads[Stage] contains values like "Lead", "MQL", "SQL"
• FactOpportunities[OpportunityId] is unique per opportunity
• FactOpportunities[Amount] is numeric currency

Core volume measures

Total Spend =
SUM ( FactSpend[SpendAmount] )
Leads =
DISTINCTCOUNT ( FactLeads[LeadId] )
MQLs =
CALCULATE (
DISTINCTCOUNT ( FactLeads[LeadId] ),
FactLeads[Stage] = "MQL"
)
SQLs =
CALCULATE (
DISTINCTCOUNT ( FactLeads[LeadId] ),
FactLeads[Stage] = "SQL"
)
Opportunities =
DISTINCTCOUNT ( FactOpportunities[OpportunityId] )
Revenue =
SUM ( FactOpportunities[Amount] )

Use DISTINCTCOUNT on identifiers to avoid double-counting when a lead or opportunity appears in multiple rows (e.g., status changes).

Efficiency and conversion

Guard against divide-by-zero with DIVIDE:

Cost per Lead =
DIVIDE ( [Total Spend], [Leads] )
Cost per MQL =
DIVIDE ( [Total Spend], [MQLs] )
Cost per Opportunity =
DIVIDE ( [Total Spend], [Opportunities] )
Lead to MQL % =
DIVIDE ( [MQLs], [Leads] )
MQL to SQL % =
DIVIDE ( [SQLs], [MQLs] )
SQL to Opportunity % =
DIVIDE ( [Opportunities], [SQLs] )
Opportunity Win Rate % =
DIVIDE (
CALCULATE (
DISTINCTCOUNT ( FactOpportunities[OpportunityId] ),
FactOpportunities[Stage] = "Closed Won"
),
[Opportunities]
)
ROI =
DIVIDE ( [Revenue] - [Total Spend], [Total Spend] )

These measures will behave correctly under filter context (channel, campaign, region, segment) as long as your relationships are set up as above.

Time Windows: Last Week vs Month-to-Date

Monday conversations are about last week and month-to-date. You can support that with measures that use DimDate flags or standard time intelligence.

Assuming the DimDate table above and that DimDate[IsCurrentWeek] and DimDate[IsCurrentMonth] are booleans:

Spend Last Week =
VAR CurrentWeekStart = STARTOFWEEK ( TODAY(), 2 )
VAR PreviousWeekStart = CurrentWeekStart - 7
VAR PreviousWeekEnd = CurrentWeekStart - 1
RETURN
CALCULATE (
    [Total Spend],
    FILTER (
        DimDate,
        DimDate[Date] >= PreviousWeekStart && DimDate[Date] <= PreviousWeekEnd
    )
)
Spend MTD =
CALCULATE (
[Total Spend],
DimDate[IsCurrentMonth] = TRUE ()
)
Leads Last Week =
VAR CurrentWeekStart = STARTOFWEEK ( TODAY(), 2 )
VAR PreviousWeekStart = CurrentWeekStart - 7
VAR PreviousWeekEnd = CurrentWeekStart - 1
RETURN
CALCULATE (
[Leads],
FILTER (
DimDate,
DimDate[Date] >= PreviousWeekStart && DimDate[Date] <= PreviousWeekEnd
)
)
Revenue MTD =
CALCULATE (
[Revenue],
DimDate[IsCurrentMonth] = TRUE ()
)

Using explicit date filters like this keeps the logic transparent when the CMO asks "what exactly does 'last week' mean?" (here: Monday–Sunday, using STARTOFWEEK with Monday as day 2).

If your organization prefers calendar weeks or fiscal periods, adjust the DimDate table with those flags and keep the measures referencing those columns.

The Monday CMO Page: Layout That Drives the Conversation

Once the model and measures are solid, the dashboard layout is straightforward. One page, three zones:

1. Top KPIs (numeric tiles)

Across the top:

• Spend Last Week
• Leads Last Week
• Opportunities Last Week
• Revenue Last Week
• Spend MTD, Revenue MTD, ROI MTD

Use card visuals bound to the measures we defined. Keep formatting consistent:

• Currency with 0–1 decimal places
• Percentages with 1–2 decimals, no more

2. Channel and Campaign performance

Two key visuals:

1. Channel performance matrix

   • Rows: DimChannel[ChannelName]
   • Columns/values: [Total Spend], [Leads], [MQLs], [Opportunities], [Revenue], [Cost per Lead], [ROI]

2. Campaign performance table

   • Rows: DimCampaign[CampaignName]
   • Values: same measures as above
   • Conditional formatting on [Cost per Lead] and [ROI] to highlight outliers

These visuals let the CMO quickly see which channels and campaigns are driving efficient pipeline.

3. Trend + Target

CMOs care about trajectory, not just snapshots. Add:

• Line chart: DimDate[Date] on axis, [Revenue] as values, filtered to the current quarter
• Target line: a measure representing quarterly pipeline target

Example target measure (hard-coded for illustration; in practice, drive this from a table so marketing ops can adjust without changing DAX):

Quarter Pipeline Target =
VAR CurrentQuarter = QUARTER ( TODAY() )
VAR CurrentYear = YEAR ( TODAY() )
RETURN
SWITCH (
    TRUE (),
    CurrentYear = YEAR ( TODAY() ) && CurrentQuarter = QUARTER ( TODAY() ), 500000,
    BLANK ()
)

Bind [Quarter Pipeline Target] as a constant line in the line chart. In production, use a dimension table like DimTargets and a measure that looks up the appropriate target based on Year and Quarter from DimDate.

Attribution and Matching: Making the Numbers Trustworthy

The hardest part for marketing analysts is matching spend to leads and opportunities in a way that stands up to scrutiny.

In this scenario, you’re not building a full multi-touch attribution model; you’re building reasonable primary-touch attribution that the CMO can understand.

Campaign keys across systems

Make sure DimCampaign can join to all sources:

• Ad platforms: campaign IDs or names
• Marketing automation: program or campaign names
• CRM: campaign, initiative, or opportunity-source fields

In Power Query, standardize campaign IDs/names before loading to the model:

let
    Source = Excel.Workbook ( File.Contents ( "CampaignMap.xlsx" ), null, true ),
    CampaignSheet = Source{[Item="CampaignMap",Kind="Sheet"]}[Data],
    PromotedHeaders = Table.PromoteHeaders ( CampaignSheet, [PromoteAllScalars=true] ),
    TrimmedNames = Table.TransformColumns (
        PromotedHeaders,
        {{"CampaignName", Text.Trim, type text}}
    ),
    UppercaseIds = Table.TransformColumns (
        TrimmedNames,
        {{"PlatformCampaignId", Text.Upper, type text}}
    )
in
    UppercaseIds

Load that as DimCampaign, then map keys in each fact table to DimCampaign[CampaignKey] in Power Query. This avoids brittle string joins in DAX.

Primary-touch attribution for leads

For a simple, defensible approach:

• Use the first marketing touch (e.g., first campaign recorded for the lead) as the primary campaign
• Store that campaign key in FactLeads[PrimaryCampaignKey]

Then in DAX, you can safely slice leads and opportunities by DimCampaign without complex attribution logic.

If your CRM tracks campaign influence on opportunities, you can extend the model later. For the Monday dashboard, keep it simple and transparent.

Performance and Governance: Things That Will Bite You Later

Marketing datasets in Chicago businesses often grow fast: more platforms, more campaigns, more history. A few guardrails keep your CMO dashboard responsive.

Keep the model in Import mode for this scenario

For a weekly CMO dashboard with data volumes typical of marketing (hundreds of thousands to a few million rows per fact table), Import mode is usually appropriate:

• Fast aggregation
• Predictable performance
• Good support for complex measures

If you connect to large cloud warehouses or need near-real-time data, you may consider DirectQuery or composite models, but that introduces latency and query limits. For a Monday dashboard where data can be refreshed overnight, Import is simpler and faster.

Refresh strategy

For this use case:

• Schedule daily refresh in the Power BI service, with a Monday early-morning slot
• Use incremental refresh for tables with date partitions if volumes grow large (requires Power BI Premium or Fabric capacity)

When configuring incremental refresh for a fact table like FactLeads:

• Ensure the table has a Date column used in the policy
• Define a reasonable historical window (e.g., last 24–36 months) and refresh period (e.g., last 7–30 days), based on how far back your CMO cares about trends

Row-level security (RLS)

If the dashboard will be used by regional leaders in addition to the CMO:

• Implement RLS on geography or business unit
• Make sure RLS filters apply consistently to all fact tables via dimensions (e.g., DimRegion)

For the CMO, you’ll typically grant access to all data, but RLS still matters for broader distribution.

Deployment: From Desktop to the CMO’s Monday Routine

Once the dashboard is built and validated:

1. Publish to a dedicated workspace used by marketing and analytics.
2. Configure data source credentials and scheduled refresh.
3. Create an app in the Power BI service with:
   • This CMO dashboard page as the first page
   • A few additional pages for deeper analysis (e.g., channel details, campaign drill-down)
4. Pin key KPIs to a Power BI dashboard (service) if the CMO prefers the classic dashboard view.

For executives, consider:

• Subscriptions: set a Monday morning email subscription with a snapshot of the key page
• Bookmarks: create a “CMO view” bookmark that locks filters to the company-wide perspective

Make sure the CMO understands that numbers in the email snapshot are point-in-time; if they open the report later in the day after a refresh, values may differ.

One Concrete Takeaway

Before you add another visual to your marketing report, sketch the Spend → Leads → Opportunities → Revenue chain and define the minimum tables and measures you need to represent it cleanly; once that model is solid, the Power BI dashboard your CMO opens every Monday becomes a single, trusted view instead of a weekly debate about whose numbers are right.