The sales team added two calls and a meeting to a deal. The forecast went up even though nobody changed the amount. If you export HubSpot data into Power BI, you can reproduce that symptom with three deals and four activity records.
Here is a small test you can run in a fresh SQLite session. The data is invented, the deals are open and all three amounts use one currency.
WITH deals(deal_id, amount) AS (
VALUES ('A', 10000), ('B', 10000), ('C', 4000)
),
activities(activity_id, deal_id) AS (
VALUES (1, 'A'), (2, 'A'), (3, 'A'), (4, 'B')
),
joined AS (
SELECT d.deal_id, d.amount, a.activity_id
FROM deals AS d
LEFT JOIN activities AS a ON a.deal_id = d.deal_id
)
SELECT
(SELECT SUM(amount) FROM deals) AS correct_pipeline,
SUM(amount) AS sum_after_join,
SUM(DISTINCT amount) AS sum_of_distinct_amounts,
COUNT(DISTINCT deal_id) AS number_of_deals,
COUNT(activity_id) AS number_of_activities
FROM joined;
| Pipeline from deals | Sum after join | Sum of distinct amounts | Deals | Activities |
|---|---|---|---|---|
| £24,000 | £44,000 | £14,000 | 3 | 4 |
Deal A contributes £30,000 after the join because it appears on three rows. Deal B legitimately has the same £10,000 amount. Deduplicating amounts throws B away. Deal C has no activity. Changing the join to an inner join would also drop its £4,000.
Keep the amount on the row it describes
In Power BI, put the deal amount on a table with one row per deal. Count activities on a separate table with one row per activity. When filtering by owner or company, check that the keys and relationships give each measure the scope you intend. A total can look plausible even when the relationship is wrong. In this example the distinct count of deals still says three beside the incorrect £44,000.
If a particular visual needs one row per deal with its number of touches, aggregate the activity rows before joining. To try this with the same data, keep the first two WITH definitions above, remove the comma after the closing parenthesis of activities, and replace the rest with this query.
SELECT
d.deal_id,
d.amount,
COALESCE(a.touch_count, 0) AS touch_count
FROM deals AS d
LEFT JOIN (
SELECT deal_id, COUNT(*) AS touch_count
FROM activities
GROUP BY deal_id
) AS a ON a.deal_id = d.deal_id;
You should get A with £10,000 and three touches, B with £10,000 and one touch, and C with £4,000 and zero touches. The amounts sum to £24,000. This example assigns each activity to one deal. If your CRM associates an activity with several deals, retain those associations separately and decide how to count shared touches.
I would keep these three cases as a refresh test: a deal with several activities, two deals with equal amounts, and a deal with no activity. They catch three different errors that can otherwise look like movements in the forecast.
I work on Datplan DataPull. Our HubSpot and Power BI reconciliation guide shows four checks you can use on your own model. Datplan used an AI agent to write this article and execute both SQL examples against the synthetic data.
Top comments (0)