218.486.5095 |  920 O’Donnell St Hawley, MN 56549

Implementation Specialists

Microsoft Gold ERP Partner in Minnesota

Microsoft

Power BI: Filtering Your Data

by

There are a few ways to filter your data within Power BI depending on how you want to view your data.

Power Query Editor Filtering

Filtering the query directly should be used to take out data that you will not need for your report. This is helpful for shrinking the size of data you are bringing into Power BI, which can improve report performance and gets rid of unnecessary information for users.

To filter the query access the Power Query Editor by selecting “Edit Queries” from the home section of the top toolbar.

Once within the editor filter any column by clicking the drop-down arrow next to the column name. Depending on the type of column there is text, number or date/time filters that you can choose from. You can also remove empties or select which values to keep from the list.

Visualization Filters

The ability to filter individual visualizations allows you to show only specific information, but still maintains all your data in the background.

To apply a filter to an individual visualization, select the visualization and add your filter options into the filters tab on the right-hand side. A visualization is selected when brackets appear around the edges. Drag the field to filter by onto the tab and choose from the types of filters, basic, advanced or top n filters. If using a date type field there is also relative date filtering available.

Here we show a basic filter where the visualization will only show records with a status of inactive.

In the below advanced filter the visualization will only show records where the tenure in months is less than 1.

The top n filtering option allows you to show the top or bottom number of records based on a particular field. In this example we are showing the top 10 customers by document amount.

Relative date filtering allows you to select a specific timeframe. The example below shows relative date filtering showing all records with an end date within the last calendar year.

Page Filters

Filtering at the page level has the same benefits as filtering at the visualization level, but it applies to the whole page. This saves time as you do not have to set up a filter on each visualization. You can apply a page level filter within the filters tab on the right-hand side. Filters can be applied to a single page or all pages in a report.

Slicers

Slicers do not filter the data for the report but gives users the ability to manually select the filter they wish to apply.

The slicer visualization is the icon with a funnel in the right-hand corner. Once you have the visualization selected pull the field over you wish to filter by.

The slicer created above creates a filter for job number. Below is what the slicer looks like on the report. When a user selects a particular job number from the list all corresponding visualizations on the page will update.

Filtering can help keep your reports streamlined yet maintain flexibility in ability to access data. It is important to think about how you want to work with your data when selecting how you will filter your report and you may even use multiple filtering options.

-Jerica Coleman, CRM and Power BI Consultant

Power BI: What are people using it for?

by

In the past few posts we’ve talked about how to connect to data and create basic reports, but a big question on a lot of people’s minds is why. Why would I take the time or invest in someone else to go through the process of connecting to data and creating reports?

Most people find it easier to tweak a process or document versus creating one from scratch and we find the same thing with software systems. Looking at how others already use the system allows you to visualize what you would change and how you could use the system within your organization.

Here are some ways customers are currently using Power BI along with a few example reports:

Sales Reports

  • Track year/quarter/month/period to date progress versus previous progress. Are you on target to exceed or fall short?
  • View sales by territory, location, salesperson and month. Focus efforts in higher sales markets and identify weaker areas for improvement.
  • Show top x number of customers or the top x items you are selling. Identify frequent or larger customers and which items are the biggest sellers.

Job Cost Reports

  • Track job P&L at a divisional level, with the ability to drill-down into locations, project managers and individual jobs. Are certain areas or people performing poorly?
  • Ability to search for individual customers or jobs to quickly see how a job is progressing.

Service Reports

  • View total service calls by division, area, month, type. Identify trends in where and why customers are contacting you.
  • Track KPI’s such as average days to complete, average days to invoice, total calls completed. Customer satisfaction lies within service, are you meeting or exceeding set KPI’s?

HR Reports

  • Track attrition by department, manager, job type, tenure, reason for leaving. Identify trends in where and why attrition is happening.
  • Monitor employee reviews due dates and completion. Employee satisfaction drives attrition, keep managers accountable for completing reviews in a timely manner.

There are hundreds of other reports that customers are using, but these are report types we see consistently used across multiple companies. Hopefully, this will give you some ideas on ways you could incorporate Power BI reports into your organization.

Interested in learning more about Power BI? Contact us!

-Jerica Coleman, CRM and Power BI Consultant

Power BI: Let’s Visualize

by

Power BI: Let’s Visualize                                                                                                

Once the data is ready to go it’s time to start creating visualizations, which is how Power BI refers to all charts, tables and data on a report.

All available tables and fields are listed on the right-hand side of the Power BI desktop and there are a couple ways to begin building visualizations.

Select any field and drag it onto the white space to start building a visual. Continue pulling fields onto the same area and Power BI will build a visualization based on the data type of the fields selected.

Change the type by selecting other visualizations from the list on the right-hand side located next to the fields list. There are several types of visualizations available, including standard column/bar charts, pie/donut charts, tables, maps and cards. Hovering over any of the visualization pictures will tell you what it is.

You can also select the type of visualization to build first. Selecting the visualization will pop a grey square onto the report. Begin pulling fields either onto the grey square or on the locations listed underneath the visualization types on the right-hand side.

IMPORTANT TIP: To work on an individual visualization make sure the brackets appear around the outside edge. If they do not appear any changes you make will not occur to the visualization.

Let’s look at the locations available and how inputting fields will affect the visualization.

AXIS:

This is the field that will separate out your totals. Adding in more fields opens the ability to drill down into the data. In the below example the chart begins at the divisional level, then adds in project manager and job number as drill down options. Selecting a column will divide that total by the next field on the hierarchy.

LEGEND:

Determines what field will separate out your data within the axis categories. In our below example we kept the axis as “Division” so that is running along the bottom and then added in project managers as the legend entry.

VALUE:

This field determines the data points on your chart. Multiple data points can be used if you want to summarize multiple field types. In our example we summarized “Contract Earned’, “Cash Over \ (Shortage) Amount” and “Over/(Under) Billed Amount” by division.

A couple notes on other non-standard chart visualization types:

  • Map visualizations: The data field to summarize is added in size instead of value. The larger the bubble the higher the field value. You must have a location field type to use the map feature.
  • Tables: All fields are entered as values and will be shown in the order they are listed underneath the value heading. You can drag and drop fields in the value list to adjust order.
  • Slicer: A slicer gives you only one field option and it serves as a filter for the remaining visualizations on the report.
  • Card: A card summarizes just one field.

Creating visualizations is the fun part of working with Power BI and it is important to get in there and really play around with how different visualizations look. In future blog posts we will be walking through applying filters and formatting visualizations.

-Jerica Coleman, CRM and Power BI Consultant

Microsoft Office 365

by

It may seem like a straight forward thing, needing a word document platform, some sort of spreadsheet program to keep data organized, the ability to make presentations in a slide by slide form factor so that you can neatly display them to a room of people. However, it goes beyond just buying a license for Microsoft Office to deliver those options to an employee or even yourself for your business. You need to worry about the hardware running the application, getting it installed correctly on that endpoint, one action works on one endpoint and not the other even though everything is the same, upgrading to a new version, add-ons and much more. Pretty soon you’re adding or fixing something a few times a week because each endpoint is its own special case, I’ve been there as an IT Service Desk Specialist.

Microsoft Office 365

For those of you that don’t want to worry about 20+ individual devices and prefer an option to pay for the service as needed instead of a full upfront license that may or may not be used immediately, there is a great alternative! Microsoft offers a service called Office 365. Important points out of the way first, this a month to month billing option for the use of the Microsoft Office applications and it is paid on a per user basis. You can add additional seats on the go as your business grows or remove them when you find yourself with an overabundance. Office 365 includes Word, Excel, PowerPoint, OneNote, Outlook, Publisher, and Access.

Microsoft Office 365 Benefits

There are quite a few benefits to using the Office platform this way as a business. The apps are hosted directly by Microsoft, you won’t have to worry about inconvenient downtime as they do upgrades and patching in a way that the service is always accessible. Because of Microsoft overseeing the hosting, you are always using the most recent version of the applications, no more having to manually upgrade yourself. Being hosted in the cloud also means that you can access those documents, spreadsheets, email, etc. wherever you are at as long as you have an internet connection. For those that rely on collaboration across multiple people or teams on projects, individuals can see the most recent version of the saved document, ensuring that the information is always up to date and can make changes in real-time. Office 365 is the most cost efficient and easily accessible way to host your office applications, offering the most return on investment to the business compared to the full license and install alternative.

Nate Freng, Sales Executive

If you’d like to explore this as an option for your business, contact us.

Four benefits of using Power BI

by

Power BI is Microsoft’s analytical service and it can be a powerful tool for any organization.

In several companies we see people taking data from different software systems and manually pulling it into an excel spreadsheet containing formulas, charts or pivot tables. These spreadsheets are then brought into management, sales and executive meetings to review how a company is performing. Not only is this a manual process taking one or more people’s time, there also is a lag from when data is entered in the initial system to when it gets in front of the right person’s eyes. Power BI can help cut down on this manual work and have data updated on a more real-time basis.

Here are some of the benefits of using Power BI:

Connect to multiple data sources

When pulling in data there are over 100 different source types to choose from. Compiling all your data into one report allows you to get an overall vision of current performance from one location.

Clean and analyze your data

Power BI gives you the ability to format, filter and transform your data. Calculated and conditional columns help analyze your data with the use of formulas and conditional statements.  Once you have clean and consistent data your reports will be more accurate and easier to read for end users.

Automatically refresh your data

Once you have reports created you can set up a schedule to refresh your data. Set a frequency, anywhere from monthly to multiple times per day, and choose the time of day you want the refresh to run so that your data is up to date when you need it.

Drill into your data

Not only can you view a company’s overall performance, but you can drill down into specific rows of data. Whether drilling down directly within your report or exporting the individual rows of data to an excel spreadsheet you can find answers quickly when a potential issue is spotted.

When you first begin using Power BI it can be overwhelming, and this is where a lot of people give up. However, if you push through the initial awkwardness you will begin to see the many benefits Power BI provides for your company.

In future blog posts we will cover the different features of Power BI to help you begin utilizing this tool effectively. You can also check out our Power BI training and webinars for more information.

-Jerica Coleman, CRM and Power BI Consultant

Have more questions on Power BI? Contact us here.