How to Create a Power BI Dashboard

Big data is a gold mine for businesses. Processing critical business data helps strengthen operations, deliver great customer service, craft personalized marketing campaigns, and take business-related actions that can boost profits.

According to a Forbes survey, 95% of organizations “face some kind of need to manage unstructured data.” And businesses that use business intelligence (BI) software for data analysis claim it makes decision-making five times faster.

That’s where Power BI comes in. This business and data analytics program by Microsoft lets businesses transform and visualize large chunks of data and provide valuable insights to make better business decisions. Power BI comes in two types: Power BI Desktop and Power BI Service.

This article walks through steps to create a dashboard in Power BI, common use cases, and best practices. We also answer whether or not you can create a dashboard in SQL or Excel.

• How to create a dashboard in Power BI
• Common use cases for a Power BI dashboard
• Power BI dashboard examples
• Can you create a Power BI dashboard in SQL or Excel?
• Power BI dashboard: best practices

How to create a dashboard in Power BI

A Power BI dashboard is a single-page canvas that tells stories through visuals like bar charts or line charts. Since it’s limited to only one page, a cohesive dashboard presents only the highlights of a story.

Note that Power BI dashboards are a feature of the Power BI Service only and aren’t available in Power BI Desktop. You can view or share dashboards on a mobile device, but you can’t create any. Below is a visual representation of a Power BI dashboard for a sales and marketing organization.

Source: Microsoft

‍

A Power BI dashboard is different from Power BI reports. A dashboard is a collection of visuals to tell a story to the end user through charts or graphs. You can create dashboards through reports. A report is a comprehensive summary of the larger data set.

Follow this step-by-step process to create a new dashboard in Power BI.

1. Select the data set you want to represent visually
2. Select the data sources
3. Create a data model
4. Establish relationships between tables
5. Visualize the data
6. Design the dashboard

‍

1. Select the data set you want to represent visually

Once you create a Power BI Service account, you can select the single data set you want to represent.

1. Click on the Get data button on the lower left side of the Home screen.

‍

2. Go to the Files section and click Get.

‍

3. Select Local File and choose the file of your choice (e.g., an Excel file).

‍

4. Once you’ve chosen the file, click on Import.

‍

5. You should see this screen with your file name.

‍

6. Once the process is complete, your data set will appear under the recent section of the Home screen. You can also find the same under the My workspace screen.

‍

2. Select the data sources

Power BI Service lets you connect to data from multiple sources, with Azure SQL Database, Azure Synapse Analytics (formerly SQL Data Warehouse), and Spark on Azure HDInsight being the most common.

However, you need to use Power BI Desktop or Excel to connect to, query, and load data into a data model. You can then import your file into Power BI, where a data set exists.

Refer to the entire list of data sources.

1. Navigate to the Home screen and click on Get data.

‍

2. Select the Get button under the Databases section. Here, you can see a list of common data types.

‍

3. Click on the relevant database or data source.

‍

3. Create a data model

1. To create a data model, click Create on the Home screen.

‍

2. You’ll have two options: Paste or manually enter the data or Pick a published dataset. Choose the option that suits you.

‍

4. Establish relationships between tables

Power BI automatically establishes relationships between tables through its autodetect feature. However, you may have to do this manually when you need to establish a relationship yourself.

If Power BI can’t determine with a proper certainty that a relationship between two tables should exist, it will not automatically create the relationship between tables.

Autodetect

‍

Manually

1. Same as above, but this time, instead of clicking on Autodetect, click on New, which will open the Create relationship dialog box.

2. Select a table in the first table drop-down list and the column you want to use.

3. In the second table drop-down list, opt for the other table you want to establish a relationship with.

4. Now, select the requisite column.

5. Once done, click OK.

5. Visualize the data

1. Open your dashboard by going to My workspace and clicking on Edit.

‍

2. Under Visualizations, you can find a large number of charts, maps, and tables to choose from.

‍

Power BI contains various default data visualization components that consist of simple line charts, area charts, and pie charts, including complex models like funnels, waterfalls, gauges, maps, and more.

Drag and drop the visuals from the right sidebar to the axis. Note that you can add multiple fields to each axis.

6. Design the dashboard

A Power BI dashboard is a single-page canvas that uses visualizations for storytelling. Since it’s restricted to one page, it contains only the highlights of a story.

So, the key things to consider in a dashboard are colors, text, images, media, and custom metrics to make sure the core elements of the story are well communicated.

This step helps you add a dashboard theme.

1. Go to your dashboard in My workspace and click Edit. Click on Dashboard theme.

‍

2. You will now see a dashboard pane. Select one of the pre-built themes. For example, light, dark or custom-built themes.

‍

3. To create a custom-built theme, select Custom in the drop-down.

‍

In case you plan to add a background image, be sure it is at least 1920x1080 resolution. In order to use an image as the background, you can upload the image to a public website. Finally, copy the URL of the image, and paste it into the Image URL field.

The second way you can create a custom theme is by uploading a JSON file which has settings for all the colors you wish to have in your dashboard.

Just like how in Power BI Desktop users can use JSON files to create themes in their reports, similarly you can upload these same JSON files for dashboards as well.

Power BI community also has a Theme Gallery Page to find and upload JSON files from.

Follow these steps to add images, text, and videos.

1. Click on + Add a tile.

‍

2. Now add the media of your choice.

‍

Common use cases for a Power BI dashboard

With data influencing everything businesses do these days, it’s important to understand how to best use tools to manage it. Let’s explore some common use cases for a Power BI dashboard.

• Customer analysis dashboard. Deep dive into product sales or region-wide business growth and profit with a customer analysis dashboard.
• HR analytics dashboard. People analytics is a top priority in over 70% of organizations. A Power BI dashboard can provide insights into metrics like total headcount, new hires, attrition rates (based on salary, education, or experience), satisfaction scores, and more.
• Sales scorecard dashboard. Tracking sales is pertinent to businesses. But with too much data, navigating through the volumes of information on sales can be difficult. A sales scorecard dashboard uses Power BI elements like treemaps, cards, and shapes so interpreting data is easier.
• Social media monitoring and analytics dashboard. Marketers, agencies, and managers need a comprehensive tool to monitor and analyze their social media presence. Power BI provides solid data on audience opinions. Get access to data around paid campaigns (like impressions, clicks, interactions, and conversions), sentiment analysis, quotes, and geolocation.
• Financial reporting. Generating ROI is a primary goal for most organizations. Tracking finances in large Excel sheets or log books can be tedious and confusing because of the huge quantity of data involved. That’s why businesses use visualization tools like Power BI. A unified dashboard provides a proof of concept and helps professionals understand revenue statements quickly.
• Team performance analysis. Effective teamwork is the cornerstone of any healthy workspace. A team performance analysis helps set targets and highlight the team’s performance levels. Leaders can evaluate how the teams performed against their goals and identify areas that require improvements or an additional push.
• Inventory management and stock analysis. Power BI can help you track stock availability, monitor inventory levels in warehouses, and replenish goods at the right time.

Power BI dashboard examples

Power BI dashboards help organizations visualize critical data. If you are new to Power BI, it provides you with samples to kickstart your journey. Here are a few examples you can check out;

‍1. Customer Profitability Dashboard

‍

A customer profitability dashboard is used to analyze a CFO's key metrics about company's executives, products, and customers. This dashboard deep-dives into a company's profitability by tapping into key metrics like number of customers, number of products, gross margin, and total revenue. Get a Customer Profitability Dashboard here.

2. Sales and Marketing Dashboard

‍

This dashboard helps understand a company's market share, product volume, sales, and sentiment around the industry. Get the Sales and Marketing Dashboard here.

3. Human Resources Dashboard

‍

The human resource dashboard helps provide information about new hires, active employees, and employees who have resigned. You also get solid understanding about who is hired, discover any potential biases in your hiring strategy, and get up-to-date information about voluntary separations. Get a Human Resources Dashboard here.

4. Retail Analysis Dashboard

‍

This dashboard helps you understand the current year's performance in comparison to last year's. Get data about sales, variance, gross margin, and analysis of new stores. Get a Retail Analysis Dashboard here.

5. Procurement Analysis Dashboard

‍

This is a great dashboard example for a manufacturing company. It helps you distill the amount spent on vendors on the basis of category and location. Learn about top vendors that provide the most discounts, total invoice, and more. Get a Procurement Analysis Dashboard here.

Can you create a Power BI dashboard in SQL or Excel?

Yes, you can use SQL or Excel to develop a Power BI dashboard.

For SQL, you’ll need to navigate to the Home tab and click on Get Data > SQL Server. Refer to this tutorial for an in-depth understanding.

For Excel, you’ll need to create data in Excel, download sample data, build a report in Power BI, and pin visuals of the report to a dashboard. Refer to this tutorial for detailed instruction.

Power BI dashboard: best practices

We’ve answered the question, “What is Power BI?” Now it’s time to make the most of your Power BI dashboard. Consider some best practices.

• Be mindful of the target. An effective Power BI dashboard should effectively communicate to the end user.

• Highlight key information. At times, you may want to present specific information in the Power BI dashboard. In that case, click on the visual, select More options, and click Spotlight in the drop-down options. All other visuals will become transparent, and the end user can pay attention to the highlighted visual.

You can also make use of slicers in Power BI. With this on-canvas visual filter, end users can filter out the report page and access the information they want to review. Learn more about how to use slicers in Power BI.

• Define a color palette. Color plays a big role in data visualization. The goal of a dashboard is to convey a story using data and insights. Using a proper color palette in your dashboard design can help you better communicate information. You may include colors in a dashboard based on background color, tile background color, tile font color, and tile opacity.

• Use space strategically. Strategic use of space is critical. You can align the visuals vertically or horizontally. Go to the Format tab, navigate to the Align menu, select Distribute horizontally or Distribute vertically, and select the visuals.

• Consider the reading order of the graphs. Once you understand the dashboard’s audience, ensure each graph and chart is placed tactically. The most important information should come first or be highlighted more noticeably than less important information. Related information should also be grouped together.

Working as a Power BI specialist

Power BI services are in demand because more and more businesses understand the potential of processing big data to provide valuable insights and help with informed decisions.

If you want to put your Power BI skills to work or jump-start your career as a Power BI specialist, consider browsing Upwork for independent Microsoft Power BI jobs that match your expertise.

It’s easy to sign up as an independent professional on Upwork, the world’s work marketplace.