How to Build a Sales Dashboard in Power BI with Dynamics 365 Data

Building a Power BI sales dashboard from Dynamics 365 data takes five steps: connect through the Dataverse connector, shape a simple star-schema model, write a handful of DAX measures, lay out visuals your sales meetings will actually use, then publish and schedule refreshes. None of it requires code beyond a few short formulas, and you can have a first working version in an afternoon.

Why bother, when Dynamics 365 has built-in charts? Because the native dashboards are fine for individual users but limited for management: cross-table analysis, targets vs actuals, and time intelligence all get much easier in Power BI. If your Monday sales meeting still starts with someone exporting to Excel, this guide is for you. Every step below uses a realistic example: a Spanish distribution company tracking pipeline and won revenue across two sales territories.

What You’ll Need Before You Start

Four things: Power BI Desktop (free download for Windows), a Dynamics 365 Sales environment with some opportunity data in it, a user account with read access to that environment, and a Power BI Pro licence if you plan to share the finished sales dashboard with colleagues. No gateway is needed because Dataverse is a cloud source.

It also helps to know which fields your team actually maintains. A sales dashboard built on estimated close dates nobody updates will mislead more than it informs. If pipeline hygiene is shaky, fix that first; features like those in our rundown of the Dynamics 365 CRM features that boost sales performance can help make good data entry the path of least resistance.

How Do You Connect Power BI to Dynamics 365?

Dynamics 365 stores its data in Dataverse, and Power BI has a native connector for it. The steps in Power BI Desktop:

1. Go to Home > Get data > Dataverse and sign in with your organisational account.
2. Pick your environment from the navigator. If you have several (dev, test, production), double-check you’re pointing at the right one.
3. Select the tables you need. For a sales dashboard that’s typically opportunity, account, and systemuser (the record owners). Resist selecting everything; each extra table slows refresh and clutters the model.
4. Choose Import rather than DirectQuery when prompted. Import copies the data into Power BI’s engine, which is faster for visuals and fine for sales data that changes over hours, not seconds.
5. Click Transform Data to open Power Query, remove the columns you won’t use, and rename tables to friendly names like “Opportunities” and “Accounts”.

One Dataverse quirk to expect: choice fields such as opportunity status arrive as numeric codes (for opportunities, statecode 0 is open, 1 is won, 2 is lost). Keep the codes for measures and add readable labels for slicers, either from the label columns the connector exposes or with a small three-row mapping table. Microsoft’s Dataverse connector documentation covers the connector’s options and limits.

Modelling the Data: Relationships and a Date Table

Aim for a simple star: the Opportunities table in the middle, with Accounts, Owners, and a Date table pointing at it. Power BI usually detects the account and owner relationships automatically (opportunity’s customer and owner lookup columns); verify them in Model view and make sure they filter in a single direction, from the lookup tables into Opportunities.

The piece beginners skip is a dedicated date table, and it’s what makes month-over-month and year-to-date calculations work. Create one with New table and a formula like Dates = CALENDAR(DATE(2023,1,1), DATE(2027,12,31)), add month and quarter columns, mark it as a date table, then relate it to the opportunity’s estimated close date. Slicing by created date instead of close date is the most common reason a pipeline chart “looks wrong” in the first sales meeting.

If your Dynamics 365 environment includes custom columns, say a “Product Line” or “Territory” choice you’ve added, bring those in too; they usually become your best slicers. (If you haven’t customised yet, our guide to customising Dynamics 365 CRM for your business needs shows how those columns get created in the first place.)

Which DAX Measures Matter for a Sales Dashboard?

Five measures cover 80% of what a sales meeting needs. Create each with New measure on the Opportunities table:

• Open Pipeline = CALCULATE(SUM(Opportunities[estimatedvalue]), Opportunities[statecode] = 0)
• Won Revenue = CALCULATE(SUM(Opportunities[actualvalue]), Opportunities[statecode] = 1)
• Won Deals = CALCULATE(COUNTROWS(Opportunities), Opportunities[statecode] = 1)
• Win Rate = DIVIDE([Won Deals], [Won Deals] + CALCULATE(COUNTROWS(Opportunities), Opportunities[statecode] = 2))
• Average Deal Size = DIVIDE([Won Revenue], [Won Deals])

Two practical notes. Measures respond to whatever filters are on the page, so “Won Revenue” automatically becomes “Won Revenue for Andalucía in Q2” when someone clicks a slicer; that’s the whole point of writing measures instead of hard-coded columns. And if you sell in multiple currencies, use the base-currency columns Dataverse maintains for you (such as estimatedvalue_base) so a dashboard mixing euro and dollar deals adds up correctly.

Building Visuals Your Sales Meetings Will Actually Use

Design for the meeting, not for decoration. A sales dashboard layout that’s proven itself in weekly sales reviews:

• Top row: four KPI cards showing Open Pipeline, Won Revenue this quarter, Win Rate, and Average Deal Size. Numbers first, always.
• A funnel chart of open pipeline by sales stage. This is where “why is €400k stuck in Proposal?” conversations start.
• A clustered column chart of Won Revenue by month, with last year alongside or a target line if you track quotas.
• A table of open opportunities sorted by value: name, account, owner, stage, estimated close date. Meetings run off lists, not charts.
• Slicers for territory, owner, and date range, kept to one edge of the page.

Skip pie charts and gauges; they eat space and answer fewer questions than a sorted bar chart. And keep it to one page at first. A sales dashboard nobody scrolls is a dashboard everybody reads. Once the team trusts the numbers, tools like Copilot can sit on top of this same model to answer ad-hoc questions in plain language, a shift we cover in how Microsoft Copilot is changing CRM for European businesses.

Publishing, Sharing, and Refresh Schedules

Click Publish in Power BI Desktop and choose a shared workspace (not “My Workspace”, which is personal). From the workspace, distribute the sales dashboard as an app so the sales team gets a clean, read-only view. Everyone viewing needs at least a Pro licence in a typical SME setup.

Then set the refresh schedule in the Power BI service: open the semantic model’s settings, confirm the Dataverse credentials, and add refresh times. Because Dataverse is a cloud source there’s no gateway to install. Pro allows up to 8 scheduled refreshes per day, which comfortably covers a 7:30 refresh before the morning meeting and another after lunch. Match the schedule to how the data is used; refreshing hourly when the team reviews weekly just burns capacity for nothing.

Finally, tell the team where the sales dashboard lives and retire the Excel export. A dashboard only changes behaviour when it becomes the single version of the truth in the room.

Frequently Asked Questions

Should I use Import or DirectQuery for Dynamics 365 data?

Import, for almost every sales dashboard. It’s faster to interact with, supports the full DAX surface, and a few scheduled refreshes a day are plenty for pipeline data. DirectQuery earns its keep only when data must be second-by-second current or is too large to import, which rarely describes SME opportunity tables.

Can I see the Power BI dashboard inside Dynamics 365?

Yes. Once published, Power BI reports can be embedded into Dynamics 365 dashboards and model-driven app pages, so salespeople see the visuals without leaving the CRM. An admin enables Power BI visualisation embedding in the environment settings first. It’s a nice adoption boost because nobody has to remember a second URL.

Do I need a paid licence to build and share this dashboard?

Building in Power BI Desktop is free. Sharing is where licensing starts: as of 2026, both publishers and viewers generally need Power BI Pro at approximately $14 per user per month, unless your organisation runs a large Fabric capacity. For a typical sales team, budget one Pro licence per person who’ll open the sales dashboard.

Why don’t my dashboard numbers match the views in Dynamics 365?

Usually one of three causes: the dashboard filters by close date while the CRM view uses created date; the model imports all opportunities while the CRM view is filtered to “My open opportunities”; or the last refresh ran before recent edits. Check date logic, filters, and refresh time in that order and the discrepancy almost always explains itself.

Getting Started

Block out an afternoon, connect Power BI Desktop to your Dynamics 365 environment, and build the one-page sales dashboard: four cards, a funnel, a revenue chart, and an opportunity table. Bring it to the next sales meeting and let the questions it raises drive version two. That feedback loop beats any amount of upfront design.

And if you’d rather compress the learning curve, AlishBit builds Power BI reporting on Dynamics 365 for businesses across Spain, from quick dashboard sprints to full data models. You can schedule a meeting with our team to talk through what your sales data could be telling you.