The short answer
Deal amount belongs on the Deal grain. Activity count, duration or type belongs on the Activity grain. If one deal is joined directly to several activity rows and its amount is then summed, Power BI can repeat the same pipeline value. Keep the facts separate and relate them through controlled dimensions or association structures.
Worked example: £10,000 becomes £30,000
Deal A has an amount of £10,000. HubSpot also records three related activities: two calls and one meeting. Flatten those records into one table and the deal amount can appear three times.
| Deal | Activity | Repeated amount |
|---|---|---|
| Deal A | Call 1 | £10,000 |
| Deal A | Meeting 1 | £10,000 |
| Deal A | Call 2 | £10,000 |
A plain SUM returns £30,000 even though the pipeline still contains one £10,000 deal.
Model Deals and Activities as different facts
A safer structure keeps one Deals fact for amount, stage and deal-level measures and a separate Activities fact for supported call, meeting or other activity measures. Company, owner, date or other suitable dimensions can provide filtering context where keys and relationships are valid.
The exact association design depends on the reporting question. The important control is that an activity relationship must not create extra deal-value rows.
Four tests before you trust the pipeline total
- Count distinct deals before and after introducing activity relationships.
- Compare total deal amount before and after the relationship.
- Filter one known deal with several activities and inspect the row set.
- Check whether a many-to-many or bidirectional relationship is creating an unexpected filter path.
Deal amount is not automatically accounting revenue
HubSpot deal amount is a CRM field. It should not be presented as recognised revenue simply because the company also uses Xero or QuickBooks. Any cross-system revenue attribution needs an explicit business rule and a separately validated downstream model.
Refresh order still matters
Run and review the HubSpot pull first, publish the prepared files, then refresh Power BI. If a supported activity object is unavailable because the authorised HubSpot scope does not permit it, expose that as a data-availability issue rather than silently treating it as zero activity.
Where Datplan fits
Datplan prepares Companies, Contacts, Deals and supported Activity reporting grains as separate local outputs. That reduces the temptation to flatten everything into one table, but the final Power BI relationships and measures remain under your control.
See the HubSpot-to-Power-BI workflow → See HubSpot API reporting coverage →
Associations are relationships, not permission to flatten everything
HubSpot associations are useful because they describe how CRM objects relate. In Power BI, however, each relationship changes the filter path through the model. Before adding an association, decide which measure it should filter and whether the relationship can create multiple paths between facts.
For pipeline reporting, a Company or Owner dimension may filter Deals cleanly. Activities can use compatible dimensions for activity analysis without copying the deal amount onto every activity row.
Why DISTINCT or clever DAX is not the first fix
A measure that uses DISTINCT can sometimes hide duplicate rows, but it can also collapse legitimate deals that happen to share the same value. The safer starting point is a model where the deal amount exists at the Deal grain and activity measures exist at the Activity grain. DAX should express the business calculation, not repair an ambiguous row model.
Check scope before interpreting a missing activity as zero
HubSpot object access depends on permissions and scopes. A report that shows no activity because an object was not available is materially different from a report showing zero activities returned from a successful supported pull. Keep source status visible enough to tell those situations apart.
That distinction matters during reconciliation and troubleshooting.