Events
Power BI DataViz World Championships
Feb 14, 4 PM - Mar 31, 4 PM
With 4 chances to enter, you could win a conference package and make it to the LIVE Grand Finale in Las Vegas
Learn moreThis browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
The Customer Profitability sample contains a dashboard, report, and semantic model for a company that manufactures marketing materials. A CFO created this dashboard to see key metrics about their five business unit managers (executives), products, customers, and gross margins (GM). At a glance, they can see what factors affect profitability.
This sample is part of a series that shows how you can use Power BI with business-oriented data, reports, and dashboards. The company obviEnce created this sample using real, anonymized data. The data is available in several formats: a built-in sample in the service, a .pbix Power BI Desktop file, or an Excel workbook. See Samples for Power BI.
This tour explores the built-in Customer Profitability sample in the Power BI service. Because the report experience is similar in Power BI Desktop and in the service, you can also follow along by using the sample .pbix file in Power BI Desktop.
Before you can use the sample, get the sample in one of the following ways:
You don't need a Power BI license to explore the samples in Power BI Desktop. You just need a Fabric free license to explore the sample in the Power BI service, and save it to your My workspace.
Open the Power BI service (app.powerbi.com
), and select Learn in the left navigation.
On the Learning center page, under Sample reports, scroll until you see the Customer Profitability Sample.
Select the sample. It opens in Reading mode.
Power BI imports the built-in sample, adding a new dashboard, report, and semantic model to your current workspace.
Select the dashboard to view the sample dashboard.
Alternatively, you can download the Customer Profitability sample as a .pbix file, which is designed for use with Power BI Desktop.
After you open the file in Power BI Desktop, select File > Publish > Publish to Power BI or choose Publish in the Home ribbon.
In the Publish to Power BI dialog, choose a workspace and then Select.
In the Power BI service, in your workspace, scroll down to the IT Spend Analysis Sample report and select to open.
From the More options (...) menu, select Pin to a dashboard. Select New dashboard, enter a name, and choose Pin live.
The dashboard that you create this way isn't the same as the sample dashboard created by the built-in sample. You can still use Q&A in the dashboard to learn more about your data and make changes to your dashboard.
If you want to view the data source for this sample, it's also available as an Excel workbook. To see the raw data, enable the Data Analysis add-ins, and then select Power Pivot > Manage.
If you want to get and use the Excel file in the Power BI service, follow these steps:
Download the sample from Power BI Desktop samples. The file is called IT Spend Analysis Sample-no-PV.xlsx.
Open the file in Excel and then select File > Publish > Publish to Power BI.
Select a workspace, such as My workspace, and choose Export.
There are different ways to work with Excel files. For more information, see Explore the Excel samples in Excel.
In the Power BI service, the exported data appears as a semantic model in the selected workspace. Select More options (...) > Auto-create report.
Select Save, enter a name for your report, and then choose Save.
From the More options (...) menu, select Pin to a dashboard. Select New dashboard, enter a name, and choose Pin live.
The dashboard and report that you create this way aren't the same as the ones created by the built-in sample. You can still use Q&A in the dashboard to explore your data and make changes to your dashboard.
With the built-in sample in the Power BI service, you have a report and a dashboard. If you obtained the sample as a .pbix or Excel file, your dashboard doesn't look as described int this section.
Let's start the tour at the dashboard. In the workspace where you saved the sample, find the Customer Profitability dashboard and select it:
Open the dashboard in the Power BI service. The dashboard tiles give the CFO a view of the high-level company metrics important to them. When they see something interesting, they can select a tile to dig into the data.
Review the tiles on the left side of the dashboard.
Note the following details:
The tiles on the right side of the dashboard provide a team scorecard. The CFO needs to keep track of their managers and these tiles give them a high-level overview of profit, by using GM%. If the GM% trend is unexpected for any manager, then they can investigate further.
By analyzing the manager-specific dashboard tiles, you can make the following observations:
This dashboard has tiles that link to a report and to an Excel workbook.
If you downloaded the Excel workbook version of this sample, you don't have the dashboard or the report, but Power BI can auto-create a report for you from the data. See Get the Excel workbook for this sample in this article for details.
Two tiles on this dashboard, Target vs Actual and Year Over Year Revenue Growth, were pinned from an Excel workbook. When you select either of these tiles, Power BI opens the data source, in this case, Excel Online.
Select Target vs Actual. Excel Online opens within the Power BI service.
The workbook has three tabs worth of data. Open COGS.
Total revenue is exceeding costs by a healthy margin. The shape of the total revenue line and height of the costs columns are similar. Interact with the data by filtering, slicing, drilling, and more. For example, look at Revenue vs COGS for just one Industry.
You see that only two district managers cover the retail industry: Andrew and Carlos.
Total revenue is exceeding costs by a healthy margin until 2014 quarter 3. And looking at the stacked column, there are some strange data that bears further examination. Did you truly have no costs for July? Did you get a refund from a third party?
Continue exploring. If you find something interesting, select Pin
from the upper-right corner to pin it to a dashboard.
Use your browser's back arrow to return to the dashboard.
Many of the tiles on the Customer Profitability sample dashboard were pinned from the underlying Customer Profitability sample report.
Select one of these tiles to open the report in Reading view.
If the tile was created in Q&A, selecting it opens the Q&A window. Select Exit Q&A to return to the dashboard and try a different tile.
The report has three pages. You can select the page you want from the Pages pane on the left.
Look at two of the team members in detail and see what insights you can gain:
In the Executive slicer on the left, select Andrew's name to filter the report page to display only data about Andrew:
Now on to Valery. In the Executive slicer, select Valery's name to filter the report page to display data only about Valery.
Continue exploring to find out why Valery's area isn't performing well. Look at regions, the other business units, and the next page in the report: Industry Margin Analysis.
This report page provides a different slice of the data. It looks at gross margin for the entire industry, broken down by segment. The CFO uses this page to compare company and business unit metrics to industry metrics to help them explain trends and profitability. You might wonder why the Gross Margin % by Month and Executive chart is on this page, because it's team-specific. Having it here lets you filter the page by business unit manager.
How does profitability vary by industry? How do the products and customers break down by industry? To answer these questions, select one or more industries from the top left. Start with the CPG industry. To clear the filter, select the eraser icon.
On the Revenue Var % to Budget, GM%, and RevenueTY by Industry bubble chart, the CFO looks for the largest bubbles, because they have the biggest effect on revenue. To easily see each manager's effect by industry segment, filter the page by selecting each manager's name in turn in the area chart.
As you select each manager in the chart, note the following details:
This page has a custom page size format, optimized for display on a mobile device..
No matter whether you downloaded the dashboard or created your own, you can use Q&A in the dashboard to discover other details in the data. Let's get more specific.
For your analysis, it might be helpful to determine which industry generates the most revenue for Valery. Use Q&A.
From the top of the dashboard, select Ask a question about your data to open the Q&A question box.
Type total revenue by industry for Valery in the question box. The visualization updates as you type the question.
As you can see, the Services industry is the biggest revenue area for Valery.
Go back to the report and take a look at the Distribution industry.
Open the Industry Margin Analysis report page.
Without selecting any visualizations on the report page, expand the filter pane, if it isn't already expanded. The Filters pane should display only page level filters.
Locate the filter for Industry and select the arrow to expand the list. Add a page filter for the Distribution industry. First, clear all selections by clearing the Select All checkbox. Then select Distribution only.
The Gross Margin % by Month and Executive chart tells you that only Valery and Tina have customers in this industry and Valery worked with this industry only from June to November.
Select Tina and then Valery in the Gross Margin by Month and Executive chart legend. Notice Tina's portion of the Total Revenue by Product chart is small compared to Valery's.
To see actual revenue, return to the dashboard, select the Q&A box, and enter total revenue by executive for distribution by scenario.
You can similarly explore other industries and even add customers to your visuals to understand causes for Valery's performance.
This environment is a safe one to play in, because you can choose not to save your changes. But if you do save them, you can always return to the Learning center for a new copy of this sample.
We hope this tour has shown how Power BI dashboards, Q&A, and reports can provide insights into sample data. Now it's your turn. Connect to your own data. With Power BI, you can connect to a wide variety of data sources. To learn more, see Get started with the Power BI service.
Events
Power BI DataViz World Championships
Feb 14, 4 PM - Mar 31, 4 PM
With 4 chances to enter, you could win a conference package and make it to the LIVE Grand Finale in Las Vegas
Learn moreTraining
Module
Explore data in Power BI - Training
Turn your business intelligence data into data insights by creating and configuring Power BI dashboards.
Certification
Microsoft Certified: Power BI Data Analyst Associate - Certifications
Demonstrate methods and best practices that align with business and technical requirements for modeling, visualizing, and analyzing data with Microsoft Power BI.