Capstone project: a sales analytics dashboard and Copilot

In this lesson we bring every part of the course together in one project. The director of a three-store chain has set the task: “When I open it each morning, I want to see the state of sales, the growth, the best city and product; and the managers should see only their own store.” We will work in stages and check the numbers at the end of each one — that is how a professional analyst works.

Stage 1: from questions to KPIs

Stage 2: data, model and the measure layer

1. 1
   Sources

   Monthly sales CSVs sit in a SharePoint folder, and Product and Store are in an Excel file stored there too. Because we chose a cloud source, no gateway will be needed later.

2. 2
   Power Query

   Combine the folder's files with Combine & Transform Data, drop unneeded columns, set types with a locale, apply Trim to the keys and move the folder path into a parameter.

3. 3
   Model

   A star schema: Sales in the middle; Product, Store, Customer and a DAX-built Date around it. All relationships are *:1 and Single; the date table is marked, Auto date/time is off and the keys are hidden.

4. 4
   Measure layer

   Create an empty _Measures table with Home › Enter data and collect every measure there, so users find them in one place. Give each measure a format and a Description.

Total Sales = SUMX ( Sales, Sales[Quantity] * Sales[UnitPrice] )
Orders = COUNTROWS ( Sales )
AOV = DIVIDE ( [Total Sales], [Orders] )
Sales LY = CALCULATE ( [Total Sales], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
YoY % = DIVIDE ( [Total Sales] - [Sales LY], [Sales LY] )
City Rank = RANKX ( ALL ( Store[City] ), [Total Sales] )
Top Product =
VAR T = TOPN ( 1, VALUES ( Product[Product] ), [Total Sales] )
RETURN CONCATENATEX ( T, Product[Product], ", " )

Stage 3: report pages

1. Overview: Total Sales, Orders, AOV and Customers cards plus the Sales YTD KPI on top; a monthly line chart in the middle (215 → 280 → 170) with a bar chart by city next to it; one date slicer.
2. Products: a Category → Product matrix with conditional formatting on % of Category; the page is also the drillthrough target for Product.
3. Customers: a customer table (Total Sales, Orders, Sales per Customer) with bookmark buttons to switch between table and chart.
4. City tooltip: a mini page that opens when you hover over a city — Total Sales, City Rank, Top Product.

Stage 4: publish, secure, refresh, app

Publish to the “Sales Analytics” workspace with Home › Publish. Create a dynamic RLS role with the rule Store[ManagerEmail] = USERPRINCIPALNAME (), add the managers to the role in the Service and check it with Test as role. Because the source lives in SharePoint, schedule the refresh for 07:00 without a gateway. Finally, publish an app with two audiences using Create app — “Directors” see every page, “Managers” see Overview and Products — and endorse the semantic model as Promoted.

Copilot in Power BI

Copilot is an assistant based on generative AI. Requirements: the workspace must be on paid Fabric capacity (F2 or higher) or Power BI Premium (P1 or higher); trial capacities and free SKUs are not supported, and a Pro or PPU licence alone is not enough. The administrator must not have turned off the Users can use Copilot and other features powered by Azure OpenAI setting. In Desktop, Copilot needs write access to a workspace on such capacity.

• Summarise a report and ask questions about the data in the Copilot pane on the right of a report.
• Create or edit a report page from a description and add a summary (narrative) visual to a page.
• Write and explain DAX queries in DAX query view and add descriptions to measures.
• A separate full-screen standalone Copilot (preview) finds and answers questions about any report or model you can access.

Make a sales report.

Create a page named Overview with cards for [Total Sales],
[Orders] and [AOV], a line chart of [Total Sales] by
'Date'[Month], and a bar chart of [Total Sales] by
Store[City] sorted descending.