A HubSpot customer service dashboard in Power BI turns support-ticket data into a clear view of workload, service performance and unresolved customer issues. Instead of reviewing individual records in HubSpot, managers can monitor trends across teams, owners, companies and ticket pipelines from one interactive report.
HubSpot provides useful tools for managing tickets and day-to-day service activity. Power BI becomes valuable when you need more flexible analysis, longer-term trends, customised KPIs or a reporting view that combines customer-service information with other business data.
In this step-by-step guide, we will use the Connectorly HubSpot data model—including tickets, ticket pipelines, contacts, companies, owners and dates—to build a practical customer service dashboard in Power BI. The finished report will help you monitor open tickets, closed tickets, ageing, ownership and workload distribution.
This guide uses customer-service data already available through Connectorly and does not depend on HubSpot marketing data. For a broader explanation of the connection, data model and reporting possibilities, read our complete guide to HubSpot reporting in Power BI.
What Should a HubSpot Customer Service Dashboard Show?
A useful customer service dashboard should help managers understand both the current support workload and how service performance changes over time. It should answer practical questions rather than simply reproduce a list of HubSpot tickets.
The dashboard we build in this guide will help answer:
- How many tickets are currently open?
- How many tickets have been closed?
- Is the unresolved ticket backlog increasing or decreasing?
- How long do tickets remain open?
- Which owners or teams have the largest workloads?
- Which ticket pipelines and stages contain the most tickets?
- Which companies or contacts are creating the most support requests?
- How are tickets distributed by priority or category, where those fields are populated?
These questions provide a practical starting point. You can later extend the report with organisation-specific service targets, escalation rules or additional HubSpot properties.
What You Need Before You Start
To follow this guide, you need an active Connectorly for HubSpot and Power BI connection and Microsoft Power BI Desktop. Connectorly extracts the supported HubSpot CRM data, organises it in a dedicated PostgreSQL database and makes it available through a reporting-ready data model.
The main tables used for this dashboard are:
- HubSpot Tickets — the central ticket records, including identifiers, created and closed dates, status, priority, category, owner and associated customer information where available.
- HubSpot Ticket Pipelines — pipeline and stage labels, ordering and closed-stage indicators.
- HubSpot Dates — the shared date table used for trends and time-based calculations.
- HubSpot Owners — information used to analyse ticket workload by owner.
- HubSpot Companies and Contacts — customer context associated with ticket records.
- Ticket association tables — bridge tables used where ticket-to-company or ticket-to-contact relationships require them.
If you begin with a Connectorly Power BI template, many of the tables and relationships may already be present. Review the existing model before creating duplicate relationships or calculations.
You will also need enough historical ticket data to make trend and ageing analysis meaningful. If your organisation has only recently started using HubSpot tickets, the dashboard will become more useful as additional records accumulate.
Step 1: Check the Ticket Data and Relationships
Before creating visuals or DAX measures, open the Power BI model view and confirm that the Connectorly ticket tables are connected correctly. Reliable relationships are essential because an incorrect join can duplicate tickets or produce misleading totals.
Check the Tickets Table
In the Connectorly model, open the hubspot tickets table and review the fields that will support the dashboard. The relevant field names include:
- Ticket ID — HubSpot’s unique identifier for the ticket.
- Subject — the ticket subject.
- Created and Created Date — when the ticket was created.
- Closed Date and Closed Date and Time — when the ticket was closed.
- Pipeline Stage ID — the current pipeline-stage identifier.
- Ticket Pipeline ID — the ticket pipeline identifier.
- Owner ID — the owner responsible for the ticket.
- Primary Company ID and Primary Company Name — the primary associated company, where one exists.
- Primary Contact ID — the primary associated contact, where one exists.
- Priority and Category — ticket classifications where these properties are populated.
- First Agent Reply — the interval before the first agent response.
- Time To Close — the interval taken to close the ticket.
- Most Relevant SLA Status and Most Relevant SLA Type — available SLA information.
- Archived — identifies archived tickets.
- Internal Link — opens the ticket in HubSpot for users who have permission.
Only use optional properties such as Priority, Category and SLA fields after checking that your HubSpot team populates them consistently.
Review the Main Relationships
The model should support these relationships:
- hubspot dates[Date] to hubspot tickets[Created Date] for ticket-creation trends.
- hubspot dates[Date] to hubspot tickets[Closed Date] as an inactive relationship for closed-ticket trends.
- hubspot owners[Owner ID] to hubspot tickets[Owner ID].
- hubspot companies[Company ID] to hubspot tickets[Primary Company ID].
- hubspot contacts[Contact ID] to hubspot tickets[Primary Contact ID].
- hubspot ticket_pipelines[Pipeline Stage ID] to hubspot tickets[Pipeline Stage ID].
Use the Is Closed field from hubspot ticket_pipelines to distinguish open and closed stages. This is more reliable than looking for a general Status field in the Tickets table.
Keep the Created Date relationship active. The Closed Date relationship should normally remain inactive and be activated inside measures that analyse ticket closures. Finally, exclude records where hubspot tickets[Archived] is true unless archived tickets are specifically required.
Step 2: Create the Core Customer Service Measures
The next step is to create the DAX measures that will power the dashboard. Store these measures in your preferred measures table. If you do not already have one, create a dedicated table so that report calculations remain organised.
Total Tickets
This measure counts unique, non-archived tickets:
Total Tickets =
CALCULATE(
DISTINCTCOUNT('hubspot tickets'[Ticket ID]),
'hubspot tickets'[Archived] = FALSE()
)Open Tickets
The Is Closed field comes from hubspot ticket_pipelines. Because that table is related to Tickets through Pipeline Stage ID, it can filter the ticket count:
Open Tickets =
CALCULATE(
[Total Tickets],
'hubspot ticket_pipelines'[Is Closed] = FALSE()
)Closed Tickets
Closed Tickets =
CALCULATE(
[Total Tickets],
'hubspot ticket_pipelines'[Is Closed] = TRUE()
)These Open Tickets and Closed Tickets measures describe the current stage of each ticket. Later, we will create a separate measure for analysing closures by Closed Date.
Average Days to Close
Connectorly provides the Time To Close interval. For a simple numeric KPI, the following measure calculates elapsed whole days using Created Date and Closed Date:
Average Days to Close =
AVERAGEX(
FILTER(
'hubspot tickets',
'hubspot tickets'[Archived] = FALSE()
&& NOT ISBLANK('hubspot tickets'[Closed Date])
),
DATEDIFF(
'hubspot tickets'[Created Date],
'hubspot tickets'[Closed Date],
DAY
)
)Average First Agent Reply in Hours
The Tickets table includes First Agent Reply as an interval and First Agent Reply Date as a datetime. This measure converts the difference between Created and First Agent Reply Date into hours:
Average First Agent Reply Hours =
AVERAGEX(
FILTER(
'hubspot tickets',
'hubspot tickets'[Archived] = FALSE()
&& NOT ISBLANK('hubspot tickets'[First Agent Reply Date])
),
DIVIDE(
DATEDIFF(
'hubspot tickets'[Created],
'hubspot tickets'[First Agent Reply Date],
MINUTE
),
60
)
)Average Age of Open Tickets
This measure calculates how many days currently open tickets have remained unresolved:
Average Open Ticket Age Days =
AVERAGEX(
FILTER(
'hubspot tickets',
'hubspot tickets'[Archived] = FALSE()
&& RELATED('hubspot ticket_pipelines'[Is Closed]) = FALSE()
&& NOT ISBLANK('hubspot tickets'[Created Date])
),
DATEDIFF(
'hubspot tickets'[Created Date],
TODAY(),
DAY
)
)Format ticket-count measures as whole numbers. Format the average duration measures with one decimal place so that the KPI cards remain easy to read.
Step 3: Build the Customer Service KPI Cards
Start the report page with a row of summary cards. These provide an immediate view of ticket volume, current workload and service speed before the reader explores the detailed charts.
Add five Card visuals and assign these measures:
- Total Tickets
- Open Tickets
- Closed Tickets
- Average Days to Close
- Average First Agent Reply Hours
If space allows, add a sixth card using Average Open Ticket Age Days. This is particularly useful for identifying whether unresolved tickets are becoming stale.
Format the Cards Clearly
- Use short labels that can be understood without explanation.
- Display ticket counts as whole numbers.
- Display duration measures with one decimal place.
- Use consistent card sizes, spacing and font styles.
- Reserve strong warning colours for measures that genuinely require attention.
A neutral design works well for Total Tickets and Closed Tickets. Open Tickets and Average Open Ticket Age can use a subtle warning colour when the values exceed an internally agreed threshold.
Decide How the Date Slicer Should Affect the Cards
Because hubspot dates[Date] is actively related to hubspot tickets[Created Date], a date slicer will normally show tickets created during the selected period.
This behaviour is useful for analysing ticket intake. However, you may want the Open Tickets card to display the complete current backlog regardless of the selected creation period. In that case, use Power BI’s Edit interactions option and disable the date slicer’s interaction with that card.
Make this decision deliberately and label the card clearly. Readers should be able to tell whether “Open Tickets” means all currently open tickets or only currently open tickets created within the selected period.
Step 4: Compare Tickets Created with Tickets Closed
A customer service dashboard should show whether new demand is arriving faster than the team can resolve it. Comparing tickets created with tickets closed helps reveal whether the backlog is likely to grow or shrink.
Create a Tickets Created Measure
Because hubspot dates[Date] is actively related to hubspot tickets[Created Date], the existing Total Tickets measure already responds to the creation date. Create a clearly named measure for use in the chart:
Tickets Created =
[Total Tickets]Create a Tickets Closed by Closed Date Measure
The relationship between Dates and Closed Date should remain inactive. This measure temporarily disables the Created Date relationship and activates the Closed Date relationship:
Tickets Closed by Closed Date =
CALCULATE(
DISTINCTCOUNT('hubspot tickets'[Ticket ID]),
'hubspot tickets'[Archived] = FALSE(),
NOT ISBLANK('hubspot tickets'[Closed Date]),
CROSSFILTER(
'hubspot dates'[Date],
'hubspot tickets'[Created Date],
NONE
),
USERELATIONSHIP(
'hubspot dates'[Date],
'hubspot tickets'[Closed Date]
)
)Build the Trend Chart
Add a Line and clustered column chart or a standard Line chart with:
- Axis: hubspot dates[Calendar Month Name]
- Values: Tickets Created and Tickets Closed by Closed Date
Sort Calendar Month Name using Calendar Month Start so that months appear chronologically rather than alphabetically.
When created tickets remain above closed tickets for several periods, unresolved demand is probably accumulating. When closures consistently exceed new tickets, the team is reducing its backlog. Review the pattern over several periods rather than drawing conclusions from one unusually busy month.
Step 5: Analyse the Open-Ticket Backlog
The total number of open tickets is useful, but managers also need to understand where those tickets are sitting and who is responsible for them. Two simple bar charts can make the backlog much easier to investigate.
Open Tickets by Pipeline Stage
Add a Bar chart with:
- Axis: hubspot ticket_pipelines[Pipeline Stage Label]
- Value: Open Tickets
Sort the stage labels using hubspot ticket_pipelines[Pipeline Stage Sort Order] where appropriate. This preserves the operational sequence defined in HubSpot instead of arranging stages alphabetically.
This visual shows where unresolved tickets are concentrated. A large number of tickets in an early stage may indicate high incoming demand, while a build-up in a later stage may reveal a review, escalation or customer-response bottleneck.
Open Tickets by Owner
Add another Bar chart with:
- Axis: hubspot owners[Owner Name]
- Value: Open Tickets
Sort the chart by Open Tickets in descending order. This makes uneven workload distribution immediately visible.
Do not assume that an owner with more tickets is underperforming. Ticket complexity, working hours, specialisation and recent assignments may explain the difference. Use the chart as a starting point for investigation rather than a standalone performance score.
Add Useful Filters
Add slicers for the fields that are populated consistently in your HubSpot portal, such as:
- hubspot ticket_pipelines[Pipeline Label]
- hubspot ticket_pipelines[Pipeline Stage Label]
- hubspot owners[Owner Name]
- hubspot tickets[Priority]
- hubspot tickets[Category]
- hubspot tickets[Connection Name] when reporting across multiple HubSpot organisations
Keep the number of slicers manageable. Every filter should help answer a real operational question rather than simply expose another available field.
Step 6: Add a Ticket Detail Table
Summary visuals help managers identify a problem, but they also need a way to find the individual tickets behind the numbers. Add a Table visual beneath the charts using:
- hubspot tickets[Ticket ID]
- hubspot tickets[Subject]
- hubspot tickets[Primary Company Name]
- hubspot owners[Owner Name]
- hubspot ticket_pipelines[Pipeline Stage Label]
- hubspot tickets[Priority]
- hubspot tickets[Created Date]
- hubspot tickets[Closed Date]
- hubspot tickets[Most Relevant SLA Status]
- hubspot tickets[Internal Link]
Remove fields such as Priority or Most Relevant SLA Status if they are not populated reliably in your HubSpot portal.
Create a Ticket Age Measure
This measure displays the elapsed number of days for each ticket. Closed tickets use Closed Date, while open tickets use today’s date:
Ticket Age Days =
VAR CreatedDate =
SELECTEDVALUE('hubspot tickets'[Created Date])
VAR EndDate =
COALESCE(
SELECTEDVALUE('hubspot tickets'[Closed Date]),
TODAY()
)
RETURN
IF(
NOT ISBLANK(CreatedDate),
DATEDIFF(CreatedDate, EndDate, DAY)
)Add Ticket Age Days to the table and sort it in descending order to bring the oldest unresolved tickets to the top. You can also apply conditional formatting to highlight tickets that have remained open beyond your organisation’s preferred response or resolution window.
Open the Ticket in HubSpot
The Internal Link field contains a link to the original HubSpot ticket for users who have the required HubSpot access. Set its data category to Web URL and display it as a URL icon to keep the table compact.
For a more detailed reporting experience, create a separate ticket drill-through page containing the ticket subject, customer, owner, stage, dates and service measures. Our guide to creating a Power BI drill-through page explains the full setup.
Step 7: Analyse Response Times and SLA Status
Ticket volume explains workload, but it does not show how quickly customers receive help. Use Connectorly’s response-time and SLA fields to add a service-performance layer to the dashboard.
First Agent Reply by Owner
Add a Bar chart with:
- Axis: hubspot owners[Owner Name]
- Value: Average First Agent Reply Hours
This visual helps identify differences in initial response times. Interpret it carefully: owners may handle different ticket types, priorities, working schedules or levels of complexity.
Average Days to Close by Category
If Category is populated consistently, add a second Bar chart with:
- Axis: hubspot tickets[Category]
- Value: Average Days to Close
This can reveal ticket categories that regularly require more time to resolve. If Category is not reliable, use hubspot ticket_pipelines[Pipeline Label] or Pipeline Stage Label instead.
Tickets by SLA Status
If your HubSpot configuration supplies meaningful SLA information, add a Column or Donut chart using:
- Legend or Axis: hubspot tickets[Most Relevant SLA Status]
- Value: Total Tickets
- Optional slicer: hubspot tickets[Most Relevant SLA Type]
Check the actual SLA values before assigning colours or labels. Use red only for a confirmed breached or overdue state, amber for a genuine warning state and neutral colours for tickets that are within target.
If the SLA fields are mostly blank, leave this visual out. A smaller dashboard built on reliable fields is more useful than a larger dashboard containing misleading service metrics.
Step 8: Arrange the Finished Dashboard
A clear layout helps readers move from the overall service position to the individual tickets that require attention. Keep the page structured and avoid filling every available space with another visual.
A practical one-page layout is:
Top Row: Filters and KPI Cards
- Date range
- Pipeline
- Owner
- Total Tickets
- Open Tickets
- Closed Tickets
- Average Days to Close
- Average First Agent Reply Hours
Middle Row: Trends and Workload
- Tickets Created versus Tickets Closed trend chart
- Open Tickets by Pipeline Stage
- Open Tickets by Owner
Bottom Row: Service Detail
- Response-time or SLA visual, where reliable data exists
- Ticket detail table
- Optional drill-through navigation
Use consistent colours across the page. For example, use one colour for created tickets, another for closed tickets and a restrained warning colour for ageing or breached items. Avoid assigning a different bright colour to every pipeline stage or owner.
Add a small “Data current as of” indicator using hubspot tickets[Data current as of]. This helps users understand when Connectorly last retrieved the underlying record information from HubSpot.
Finally, test every slicer and chart interaction. Selecting an owner, company, stage or month should filter the relevant visuals without producing unexplained totals or blank results.
Step 9: Validate the Dashboard Against HubSpot
Before sharing the dashboard, compare its results with HubSpot. Validation is especially important when the report uses several relationships, date fields and pipeline-stage rules.
Start with a Small Test Period
Select a recent date range and compare the number of tickets created with a matching HubSpot ticket view. Make sure both systems use the same:
- Date range
- HubSpot organisation
- Ticket pipeline
- Archived-record treatment
- Owner or team filters
Check Open and Closed Logic
Confirm that every relevant value of hubspot ticket_pipelines[Pipeline Stage Label] has the expected Is Closed value. If a custom HubSpot stage is classified incorrectly, the Open Tickets and Closed Tickets measures will also be incorrect.
Check Date Behaviour
Tickets Created should respond to Created Date, while Tickets Closed by Closed Date should respond to Closed Date. Test both measures using a small number of known records.
Also review your organisation’s timezone settings. A ticket created or closed close to midnight may appear on a neighbouring calendar date when systems use different timezones.
Investigate Blank Customer or Owner Values
Some tickets may not have a Primary Company ID, Primary Contact ID or Owner ID. These are not necessarily data errors. They may represent tickets that have not been associated or assigned in HubSpot.
Keep unassigned or unassociated records visible during validation. Hiding blanks too early can make dashboard totals appear lower than the source data.
Document the dashboard’s definitions after validation. Users should know exactly what counts as open, closed, archived, created, resolved and within the selected reporting period.
Common Customer Service Dashboard Mistakes to Avoid
Counting Rows Instead of Ticket IDs
Use a distinct count of hubspot tickets[Ticket ID]. Relationships or association data can create multiple rows in some reporting scenarios, so a simple row count may overstate ticket volume.
Looking for a Status Field in Tickets
The Connectorly Tickets table does not contain a general ticket Status field. Use hubspot tickets[Pipeline Stage ID] with the related hubspot ticket_pipelines table. The Is Closed field identifies whether the current stage is open or closed.
Using Created Date for Closure Trends
A ticket may be created in one month and closed in another. Use the inactive relationship between hubspot dates[Date] and hubspot tickets[Closed Date] when analysing closures.
Treating Blank Associations as Missing Tickets
A ticket can exist without a Primary Company ID, Primary Contact ID or Owner ID. Keep these records visible and label them as unassigned or unassociated where appropriate.
Assuming Every Optional Property Is Reliable
Priority, Category and SLA fields are only useful when teams populate them consistently. Check their completeness before making them central to the dashboard.
Comparing Owners Without Context
Ticket counts and response times do not automatically measure individual performance. Consider ticket complexity, specialisation, working hours, assignment rules and customer dependencies before drawing conclusions.
Adding Too Many Visuals
A focused dashboard should help users recognise workload, trends, bottlenecks and urgent tickets quickly. Additional charts should only be added when they support a defined decision or action.
Final Thoughts
A well-designed HubSpot customer service dashboard in Power BI gives managers a clearer view of ticket demand, open workload, response times, ownership and unresolved customer issues.
The most important part of the process is not the visual design. It is building the report on reliable identifiers, relationships and date logic. Ticket ID should drive the counts, Pipeline Stage ID should determine the current stage, Is Closed should distinguish open and closed stages, and Created Date and Closed Date should support their respective trends.
Connectorly provides the underlying HubSpot data in a structured reporting model, including Tickets, Ticket Pipelines, Owners, Companies, Contacts and Dates. This gives Power BI users a strong starting point while preserving the flexibility to create dashboards around their organisation’s own service processes and definitions.
Begin with the core measures and a small number of useful visuals. Validate the results against HubSpot, document the definitions and only add optional metrics such as Priority, Category or SLA performance when those properties contain reliable data.
If you are ready to build reporting beyond HubSpot’s standard dashboards, explore Connectorly for HubSpot and Power BI.
Frequently Asked Questions
Can I build a HubSpot customer service dashboard in Power BI?
Yes. HubSpot ticket data can be analysed in Power BI to report on ticket volume, open workload, closures, response times, owners, customers, pipeline stages and SLA information. Connectorly provides supported HubSpot data in a structured PostgreSQL data model for Power BI reporting.
Which Connectorly tables are used for customer service reporting?
The main tables are hubspot tickets, hubspot ticket_pipelines, hubspot dates, hubspot owners, hubspot companies and hubspot contacts. Tickets contains the central ticket records, while the related tables provide pipeline, date, owner and customer context.
How do I identify open and closed HubSpot tickets?
Relate hubspot tickets[Pipeline Stage ID] to hubspot ticket_pipelines[Pipeline Stage ID]. Then use hubspot ticket_pipelines[Is Closed] to distinguish open and closed stages. The Tickets table does not contain a general Status field.
Should ticket trends use Created Date or Closed Date?
Use Created Date to analyse incoming ticket demand and Closed Date to analyse ticket closures. Keep the Created Date relationship to the Dates table active and normally keep the Closed Date relationship inactive, activating it inside the appropriate DAX measure.
Can Power BI report on HubSpot first-response time?
Yes. The Connectorly Tickets table includes First Agent Reply as an interval and First Agent Reply Date as a datetime. You can calculate average response time by comparing Created with First Agent Reply Date.
Can I report on HubSpot SLA performance?
The Tickets table includes Most Relevant SLA Status and Most Relevant SLA Type. These fields can support SLA reporting when they are populated consistently in the HubSpot portal. Check the available values before creating targets or colour rules.
Why are some tickets missing a company, contact or owner?
A HubSpot ticket may not have a Primary Company ID, Primary Contact ID or Owner ID. These tickets should normally remain visible and can be labelled as unassociated or unassigned rather than excluded from the report.
Can users open the original HubSpot ticket from Power BI?
Yes. The hubspot tickets[Internal Link] field contains a link to the source ticket. Set the field’s data category to Web URL and display it as a link or icon. The user must also have permission to access the ticket in HubSpot.
Can the dashboard report across multiple HubSpot organisations?
Connectorly is designed to support reporting across multiple HubSpot organisations. Use Connection Name to identify the source organisation and include it as a slicer when several connections are combined in the reporting model.
