One Metric, Two Revenue Models: Distinct Counting in DAX

The business

WeLearn is an EdTech scaleup that partners with content creators and takes a share of what they earn. The commercial side of the company is organised around account managers, each responsible for a book of creators, grouped into teams. Every quarter, each account manager carries a revenue target.

That sounds like a straightforward sales report. It is not, because of how the money actually moves.

Why the reporting was hard

Four things complicated every number on the report.

• Two revenue mechanisms. Some revenue arrives as one-off transactions. Some arrives as installment plans agreed once and paid across several months — so a plan signed in January is still producing revenue in April with no April transaction to prove it.
• Revenue is shared. A percentage goes back to the creator. If the share is 50%, both the revenue and the costs are split 50/50, and only the remainder is WeLearn’s.
• Costs are stored as transactions. Covered expenses sit in the same table as revenue, carrying a negative sign — so any measure that forgets them overstates performance, and any measure that subtracts them twice understates it.
• Two systems held the truth. Part of the commercial data lived in the CRM, part in operational spreadsheets, and some creators appeared in both. Their totals had to agree exactly.

What the report had to answer

Two audiences, wanting different things from the same model.

Leadership needed the quarter at a glance: how much has been collected against target, how many partners are actively generating revenue, and whether the trend is holding. Account managers needed their own book — their targets, their creators, and enough warning to act before the quarter closed rather than explain afterwards.

The report is scoped to one team at a time, with account manager and team selections driving everything on the page.

What got built

Five pages, refreshed nightly, filterable by account manager, team, creator and period.

• Creators — performance per individual creator, with attainment against the current quarter’s target and a rolling twelve-month revenue trend.
• Launches — launch quality rather than volume: what share cleared the high-performing threshold, plus first-launch tracking for new creators.
• Webinars — per-event profitability, sortable to surface loss-making events, with targets tracked on both value and count.
• Leaderboard — account managers ranked, revenue broken down by product type (courses, webinars, memberships, affiliate), with a margin benchmark.
• Cross-team — the whole-company view: revenue and margin per creator across every team, live roster status, and revenue by niche over time.

The same six KPIs appear on every page, defined once: revenue generated, operative margin, margin percentage, net achieved, target, and target achievement. Consistency across pages was a deliberate constraint — the fastest way to lose a commercial team’s trust is to show them two pages where the same word means two different things.

The Creators page, reconstructed

No screenshots or recordings of the live report appear anywhere on this site. The real dashboard carries company financials, named account managers and individually identifiable creators with their earnings, so it cannot be published — and redacting it would leave an empty frame rather than anything worth looking at. What follows is a rebuild of the layout with entirely invented data, so the structure can be shown without the contents.

Three things drove that layout. The KPI row stays fixed at the top so the same three numbers frame every question asked underneath. The gauge answers “where am I against this quarter” in one glance, with the quarterly history beside it so a good quarter cannot be mistaken for a good year. And the twelve-month trend sits along the bottom of every relevant page, because a single month means very little on its own.

Operative margin is revenue after refunds and operating costs — advertising, platform fees and creator salaries — which is why the margin figure and the revenue figure tell genuinely different stories about the same creator.

Targets: generated versus collected

The single most important distinction on the whole report. The commercial view measures revenue generated — dated when a transaction is created. Target attainment measures revenue collected — dated when the money is actually received.

Those two dates disagree by design, and both are correct. A quarter can look strong on generated revenue and weak on collections, and an account manager needs to see which. Most of the debugging on this project traced back to a measure silently using the wrong one.

Targets themselves come from a quarterly planning sheet — period, year, creator, target value, mapped account manager and team — loaded through Power Query and joined into the model. Attainment is then evaluated per quarter against collections, net of the creator’s share, expenses and any discount rates.

Order of operations mattered more than the formulas. Net first, then the revenue share, then discounts. Applied in any other sequence, two systems holding the same underlying truth still disagree by a few percent — which is worse than disagreeing by a lot, because nobody notices.

Counting active partners across both revenue models

Leadership wanted one number: how many partners actually generated revenue this month. A partner might appear in either revenue stream or both, and must be counted once. A partner whose installment plan runs through the month counts, even though nothing was transacted in it.

The measures below are generalised — table and column names are illustrative, not WeLearn’s.

Deciding who counts as active

“Active” could not simply be read off a flag. A partner counts if their status is current, or if they were dropped but the drop happened after the month being viewed.

That second clause is the whole point. Filtering on today’s status makes history unstable: run the report in June and again in September and they disagree about March, because someone churned in between. Evaluating status as at the selected period freezes history.

VAR _lastDay = EOMONTH( MAXX( ALLSELECTED( 'Calendar' ), 'Calendar'[Date] ), 0 )
VAR _firstDayNextMonth = _lastDay + 1

VAR _activePartners =
    FILTER(
        ALL( 'Partner'[Partner_ID] ),
        CALCULATE( MAX( 'Partner'[Status] ) ) = "Active"
            || CALCULATE( MAX( 'Partner'[Dropped_Date] ) ) >= _firstDayNextMonth
    )

The installment window

A plan is live in the selected month if it started on or before the month ends, and its final installment falls on or after the month begins. Both conditions are needed: the first alone counts plans that finished years ago, the second alone counts plans that have not started.

FILTER(
    'RecurringSales',
    'RecurringSales'[Start_Date] <= _lastDay
        && EOMONTH( 'RecurringSales'[Start_Date], 'RecurringSales'[Installments] - 1 ) >= _firstDaySelectedMonth
        && 'RecurringSales'[Total_Value] >= _threshold
)

EOMONTH(start, installments - 1) derives the plan’s final month from its start and length, so no end date has to be stored or kept in step with the source. The - 1 matters: a three-month plan starting in January ends in March, not April.

Filters across an unrelated table

Account manager and team selections came from the transactional fact table, but the recurring-sales table carried its own copies of those columns and no physical relationship. Building one would have introduced ambiguity — two paths to the same dimension at different grains. TREATAS applies the selection as a filter without any modelled relationship existing.

CALCULATETABLE(
    DISTINCT( 'RecurringSales'[Partner_ID] ),
    TREATAS( _selectedManagers, 'RecurringSales'[Account_Manager] ),
    TREATAS( _selectedTeams,    'RecurringSales'[Team] ),
    ...
)

Set logic instead of arithmetic

Counting each stream and adding them double-counts every partner in both. Subtracting an overlap is fragile, because the overlap must be recomputed under exactly the same filter context — and it breaks quietly the first time a slicer is added.

Set operations remove the problem. INTERSECT each revenue stream with the active list, UNION the results, count the distinct rows. Single-counting becomes a property of the structure rather than a correction someone has to maintain.

RETURN
COUNTROWS(
    DISTINCT(
        UNION(
            INTERSECT( _activePartners, _transactionSellers ),
            INTERSECT( _activePartners, _recurringSellers )
        )
    )
)

Reconciling the two source systems

Some creators existed in the CRM, some in operational spreadsheets, some in both. The requirement was blunt: where a creator appears in both, the totals must agree exactly.

Reaching that meant aligning definitions before aligning numbers — which figure is gross, which is net of the creator’s share, where covered expenses are subtracted, and at what point discount rates apply. The reconciliation surfaced genuine discrepancies in both directions, and the fix was almost always a definition, not a formula.

Two audiences, one model

The first build served leadership: quarter-to-date collections against target on a gauge, active partner counts, and trend. Once that was trusted, the same model was rebuilt for account managers — their own book, their own targets, and a per-creator table showing who is tracking behind while there is still quarter left to act.

Same measures, same grain, different entry point. Rebuilding the presentation rather than the model is what keeps two audiences arguing about strategy instead of arguing about whose number is right.

Getting it adopted

A dashboard nobody opens is worth nothing, so the rollout came with a walkthrough deck for the account-manager team: what each page answers, how the six KPIs are defined, and three habits to start with — set your filters first, read the gauge for quarter progress, and never judge on revenue alone without checking margin and collections beside it.

It also included a short primer on Power BI itself, which turned out to matter more than expected. Ctrl-click for multi-select. Cross-highlighting by clicking into a visual. Exporting to Excel. And resetting your filters when you finish, because these are shared live reports and the next person inherits whatever view you left behind.

How it was built

Why status had to be evaluated as at the period

Reading a partner’s current status makes every historical month unstable — run the report in June and again in September and they disagree about March, because someone churned in between. Treating a partner as active if they were dropped only after the selected month ends freezes history, which is the difference between a report people trust and one they re-check by hand.

Set operations beat add-then-subtract

Counting each revenue stream and summing them double-counts anyone present in both; subtracting the overlap means recomputing that overlap under exactly the same filter context, which breaks quietly the first time a new slicer is added. UNION over two INTERSECTs makes single-counting a property of the structure rather than something a correction term has to maintain.

The end date nobody had to store

An installment plan’s last month is derivable from its start and its length, so no end date needs to exist in the source or be kept in step with it. Everything then reduces to a window-overlap test: started on or before the month closes, still running when it opens.