218.486.5095 |  920 O’Donnell St Hawley, MN 56549

Implementation Specialists

Microsoft Gold ERP Partner in Minnesota

Power BI

Why Power BI? Sharing Capabilities

by

Reporting and analytics are only useful if you can share them with the people that need to know and can take action on the results. Let’s take a look at a few ways that you can share data with Power BI.

Sharing report file from Power BI Desktop

This is the only option that does not require a Power BI pro license. Once you have created a report within the Power BI Desktop the report can be saved as a PBIX file. This file can be sent to others or saved in a shared drive for access. Because this exists within the Power BI Desktop the visuals cannot be placed within a dashboard and cannot be automatically refreshed. Data can be manually refreshed or the report can be created using direct query as the method for connecting to the data.

Sharing individual reports/dashboards in Power BI Online

Reports can be shared both within and outside your organization with Power BI online. All users that need to view the report will require a Power BI Pro license or the report must be created with Power BI Premium capacity. Reports can be shared individually with one or more people or they can be published to a workspace. Workspaces are helpful to create when you have a group of people that will need access to more than one report as this eliminates the need to go through the sharing process with each report for multiple people.

Sharing via Teams

Microsoft Teams has a Power BI tab that can be added to your app and once this is activated and users are signed in reports can be shared. Users are required to have a Power BI Pro license or the report must be created with the Power BI Premium capacity to be shared via Teams. Teams is helpful to use if you are already using it as a communication tool as users can view reports within a system they are already accessing on a daily basis and collaboration can be even easier.

Sharing via SharePoint

Reports can also be embedded into SharePoint online. In order to publish to SharePoint a report must first be published to Power BI online and all users that need to view the report will require a Power BI Pro license. If your team uses SharePoint on a daily basis this can be a great option to avoid having to navigate to Power BI online to view reports.

These are a few ways to share data with Power BI and are the main ways I use with customers. There are quite a few others, such as exporting to excel or PowerPoint and even publishing to the web. (Caution: Publishing to the web can be a security risk as data published to the web can be viewed by anyone on the internet.)

Getting data into the right hands as efficiently as possible is a huge asset for any company and with many options available Power BI is an excellent tool to help with this. Contact us if you have any questions on Power BI.

-Jerica Coleman, CRM and Power BI Consultant

 

Why Power BI? Connect to Multiple Sources

by

When the manager wants to know how their division is performing or the CEO wants to know the overall health of the business what needs to be done?

Do you have to pull up a revenue report from an accounting system, a sales report from a CRM system, a productivity report from a performance tracking system? Perhaps you have a different set of reports to run, but a common complaint I hear from customers is that it is so difficult to know how a business is performing when you have to pull bits and pieces from multiple systems. More often with this type of reporting responses to business problems are reactionary versus proactive.

With Power BI you can connect to databases, such as SQL and Azure, files, such as Excel or CSV/Text, online services, such as Dynamics CRM, Salesforce and Sharepoint along with many more. Pull in data from multiple sources and view data points on a single dashboard or report.

Enabling customers to quickly view and analyze their data allows them to spend more time running their business versus running reports. No more piecing information together from multiple systems, no more combining and manipulating data in excel, no more long hours pulling together weekly, monthly or quarterly results for an operational meeting. With Power BI you set up connections, build the reports and then share with colleagues. At that point any of those working with the data can pull up the report and see how the business is performing.

Here is an example of a sales report pulling from a CRM and an accounting system to show current versus previous sales progress.

Here is an example of a HR report pulling from an employee database and an excel file showing current employee attrition.

These are two basic examples of pulling together more than one set of data, but there are so many more options. For a complete list of data sources you can connect to select “Get data” from the toolbar within Power BI desktop.

Even though there are many others benefits to using CRM this will always be the first thing I mention to customers because it is such an important benefit. Almost all the companies I have worked with have at least two systems they work with and can benefit from pulling the data into a single dashboard. If you are faced with this same issue it’s time to take a look at Power BI. Contact us to learn more.

-Jerica Coleman, CRM and Power BI Consultant

Why Power BI

by

Welcome to our month of Why Power BI! This month, we are focusing on why you should be using Microsoft’s Power BI and how to get you started with this powerful analytical tool.

You’ve probably heard of Power BI and know what it is, but do you know why exactly your company should be using it? Check out this blog post featuring four benefits of using Power BI. Not only does Power BI update on a real time basis, but you can feel rest assured that your data is accurate without having to enter data manually into a spreadsheet.

Are you wondering what sources can connect to Power BI? This blog post breaks down the hundreds of different sources you can connect with Power BI. Learn more about Power BI Desktop vs. Power BI Online. Once you understand this information, you’ll be able to make reports and visualizations; however, you’ll want to clean up your data before creating your full report. Read how to clean up your data here.

Check out more of our past blog posts on Power BI:

Let’s Visualize

Power BI: What are people using it for?

Getting Started Tips

Data Modeling

Filtering Your Data

Conditional Formatting

Don’t miss our Getting Started with Power BI webinar on July 8th. Register here.

Still have questions on Power BI? We’d love to help you. Contact us today!

 

Power BI: Automatic Refreshes

by

One of the main things customers want when looking at data analysis and reporting tools is to have data that is up to date without needing someone to run the report. An awesome feature of Power BI is the ability to schedule automatic refreshes multiple times a day.

Power BI Pro or Power BI Premium Licensing Required

The ability to schedule automatic refreshes requires you to have a Power BI Pro license as this is managed within the online Power BI system. With the Pro version you are allotted up to 8 refreshes per day. If a dataset will require more refreshes per day Power BI Premium allows you to have up to 48 per day, there is significant price increase to Premium.

Install and Configure Gateway

In order to schedule a data refresh a gateway must be installed and configured. Either a personal or on-premise gateway can be utilized, but it is important to note that the computer using the gateway must be on and within the company network at the time of the scheduled refresh for it to work.

Schedule Refreshes

To schedule a refresh access the dataset and select the schedule refresh icon as shown in the screenshot below.

On the next screen expand the “Scheduled refresh” tab and fill out refresh settings. Once everything is filled out select “Apply” to schedule your refresh.

Viewing Refresh Status

Within the dataset list you can view when the dataset was last refreshed and when the next refresh is scheduled for. To view this let’s return to the dataset list. Select the workspace where the dataset is located and then select the “Dataset + dataflows” tab.

In our example above we can see the last date each dataset was refreshed and that three of them have upcoming scheduled refreshes. We also see one error identified by a triangle and exclamation point next to the refreshed date for the Sales Report Example. To view the error select the error icon.

Here we can see that the data gateway was offline when the refresh was attempted. If an error occurs with your refresh you can manually refresh the data at that point or clear up the error and wait for the next scheduled refresh.

Report Size

It is important to note that refreshes do use system resources and can take quite a bit of time depending on the overall size of your dataset. If you are pulling hundreds of thousands of rows data refreshes can take upwards of 15-20 minutes.

Make sure you are setting your data refreshes to occur after all data has been entered for the day to ensure all changes are pulled in, if the report is refreshing at the same time data is being entered it will not be the most up to date.

With larger sets of data from a database, such as SQL or Azure, make sure and add in a longer command timeout. This is done when connecting to the data source during initial creation of the report. It can also be adjusted after a report is created by changing data source settings within the Power BI Desktop. I recommend setting the timeout minutes to 30 or 60 minutes.

Data refreshes take away the need for someone to manually go in and run the report. Reports and dashboards can be accessed for weekly meetings or reviewed in the morning with up to date data. When reporting and analytics are efficient important business decisions can be made quickly to improve and grow your business.

Have questions on Power BI or want more information? Contact us to learn more.

-Jerica Coleman, CRM and Power BI Consultant

Power BI: Basic Report Formatting

by

Usually the primary focus of creating Power BI reports entails getting connected to data, creating visuals and analytics and overall report aesthetics are an afterthought. Although, this isn’t a high priority item I would suggest spending some time planning overall report design. Having consistent and clean formatting will give your reports a more professional and polished appearance.

Each visual type has different characteristics that can be customized. An entire novel could be written to cover each formatting option. Today we are going to focus on more general formatting for reports.

Here is a screenshot of our report before any formatting has been applied:

Even though the report doesn’t look bad it can definitely be better.

Renaming Fields Within a Visual

Field names default to the schema name coming from the data source and may not always make sense. To rename a visual choose the down arrow next to the field name on the visualizations tab and select rename.

Here is how the report looks with all the fields renamed. The new names make it a lot easier to understand what is shown.

Titles

Some visuals have headers and some use the title, so changing to all titles can make visuals more uniform. Titles are accessed by selecting the paint roller icon on the visualization tab. Make sure the slider is switched to on.

Expand the title options by selecting the down arrow to the left of the word and you can free form your title text, format font and give a background color to the title line. Here is how the report looks now that we have added titles to the main visualizations.

Background

Adding a background to your visual can make it pop off the page. This is also accessed by selecting the paint roller icon. Make sure the slider is set to on and expand the background option to select color and transparency. In our screenshot we applied the background to our price visual.

Border or Shadow

Both of these options allow you to set the area around a visual and are helpful when you have a lot of visuals or if you like clearly defined boundaries. Access these options by selecting the paint roller icon. Set the slider to on and choose from options available. Here we have added a border to the sales transaction table and a shadow to the price by state map.

Data Colors

Colors can be formatted for charts individually or by choosing themes. If your company has pantone colors it is popular to customize to your company colors. Access the option to individually change data colors by selecting the paint roller icon and expand the data colors option.

Themes are available under the view tab on the toolbar.

Here is what our report looks like now that I have switched to the colorblind safe theme.

Data Labels

Although you can view the data points by hovering over the charts sometimes it is easier to add the data labels on the chart. This option is available by selecting the paint roller icon. Turn the slider to on and set the options. The units display defaults to auto. If you want the full number visible change to none or choose a common unit abbreviation like millions.

We added data labels to our two charts and set the units to none.

Tooltip

When tooltips are set to on you can add additional fields that will show up in a box when you hover over a data point. Turn tooltips on by selecting the paint roller icon and set the slider to on. Once tooltips are set pull the fields you want to appear under the tooltips section on the visual field tab.

We added and renamed two fields to our tooltip section and here is what it looks like when we hover over a data point:

Format Painter

Once you have set a basic formatting for visuals you can copy the formatting to other visuals using the format painter. It is important to note that you can only copy to one visual at a time, so once you have copied over your formatting to one visual you will need to select format painter again to copy the formatting over to the next visual. If you added a title the formatting will be applied, but you will still need to open the option within each visual to set the wording.

The format painter is available in the main toolbar.

Here is what our finalized report looks like now that we applied the same formatting to all our charts and tables:

In this example we didn’t apply formatting to our slicers (filters) on the page, but all the same formatting could be applied to those as well.

Happy Formatting!

-Jerica Coleman, CRM and Power BI Consultant

Interested in learning more about Power BI? Check out our other Power BI posts here.

Power BI: Using Slicers

by

Slicers are an interactive filter that can be placed on a Power BI report. Only one field can be used per slicer, but multiple slicers can be used within a report.

It is important to remember that data can be filtered by selecting a data point on a visual as well, so a slicer may not always be necessary. In the screenshot below we see the report as it normally appears on the left; however, on the right we see how the report visuals change when the south service area was selected from the bottom visual.

Let’s look at some of the options available with slicers.

Selection Controls

There are a few options regarding what can be selected within a slicer. These options are available on the formatting tab, that is accessed by selecting the paint roller icon on the visualization tab.

  • Single Select
    • Users can only select one option within the slicer. This defaults to off, select the slider to turn on.
  • Multi-select with CTRL
    • Users will be allowed to select multiple options by depressing the control key. This defaults to on, select the slider to turn off.
    • When both this option and the single select options are turned off users can select multiple options by clicking on them.
  • Show “Select all” option
    • A select all box will appear at the top of the list. This defaults to off, select the slider to turn on.
    • With slicers if nothing is selected the report will show all results set for that report page, so generally I find the select all option to be unnecessary.

Formatting

Formatting options that are similar across visual types, such as colors, borders and headers will not be covered here. We are looking at formatting specific to the slicer visual.

  • Orientation
    • Can be changed on the formatting tab under “General”.
    • Horizontal
      • Options will be displayed as buttons that can be selected.
    • Vertical
      • Options will be displayed as a list with check-boxes to the left side.

  • List versus Dropdown
    • Available for slicers with a vertical orientation. Can be changed within the slicer itself by selecting the drop-down arrow that appears when hovering over the slicer.

  • List
    • All options will appear within a list. A scrollbar will appear if there are too many options to fit within the slicer size.
  • Dropdown
    • One dropdown box will be shown, and all options can be accessed by selecting the arrow within the box. This allows slicers to take up less room than a list.
  • Search Box
    • Can be added within by selecting the ellipses (three dots) when the slicer is selected.

  • Adds a search box at the top of the slicer list and allows end users to search for a particular value. This is beneficial when lists are long, such as customer or item lists.

Syncing Slicers

Slicers can be used across multiple pages and synced together.

To access the sync slicer tab go to “View” and then “Sync Slicers”.

Within this tab a slicer can be chosen to appear on multiple pages and if the slicer will sync between the pages. In the screenshot below we have set our slicer to appear on all three pages of the report. It has also been set to sync between the sales page and page 1 of the report so if a selection is made on either of those pages it will update both pages.

Slicers can be a highly beneficial tool to use within your Power BI report. When designing a report take some time to think about what slicers would be needed for an end user. Have slicers that will allow the end users to dig into or analyze the data easily, but avoid having too many that will clutter up your report.

Have questions on Power BI or want more information? Contact us to learn more.

-Jerica Coleman, CRM and Power BI Consultant