218.486.5095 |  920 O’Donnell St Hawley, MN 56549

Implementation Specialists

Microsoft Gold ERP Partner in Minnesota

Microsoft Power BI

Power BI: Conditional Formatting Continued

by

In our last Power BI blog post we talked about applying conditional formatting to a matrix or table within reports. Today we are going to look at a few more types of conditional formatting that can be used.

Data Bars Example

Adding data bars helps to quickly identify high and low performance.

In this example we are adding data bars to the profit column.

  • Show Bar Only
    • If selected the values would not appear as numbers within the table or matrix, just the bar would appear. We have not selected this.
  • Minimum and Maximum
    • Defaults to lowest and highest values, but custom values can also be set. Custom values are most commonly used when data bars are used to measure against goals. We kept the range as lowest and highest values.
  • Colors
    • Colors for positive, negative and the axis can be customized.
  • Bar Direction
    • Can be left to right or right to left. As we want negative values to the left we have kept bar direction as left to right.

Here is how our table looks now:

Web URL Example

To add a web URL link there must be a column within one of the tables that is a URL. We have added the column weather to our table and these URL’s are links to weather.com for the city where the project is located.

Here is what the data table shows:

To avoid showing the long URL within our visualization table we are going to apply conditional formatting to the project column of our visual and select web URL.

To avoid showing the long URL within our visualization table we are going to apply conditional formatting to the project column of our visual and select web URL.

  • Format by
    • Defaults to field value as the URL within the column will be used.
  • Based on field
    • Weather column was selected as it holds the URL.
  • Summarization
    • Set to use the first value within that column for each row.

Here is how our visual table looks now:

Each project has a line underneath signifying that it is a hyperlink and hovering over the project shows the full link that will be navigated to if that project is selected.

Field Value Example

In the Web URL example we used the URL field value to apply formatting. This can be done with other field values when they are a type of formatting. In the next example we are going to use color.

In the data table a conditional column was created that color coded the project by profitability. Anything $0 or less is red, greater than $3000 is green and greater than $0 is yellow.

Here is how the data table looks:

Now let’s use this new column to apply formatting.

We have selected to format the background color of the profit column.

  • Format by
    • This is switched to field value.
  • Based on field
    • The color code column created is used to format the color.
  • Summarization
    • The first value is what will be used. As we do not have any duplicate project numbers there is only one value for each project.

Here is how our table looks now:

The same formatting could be applied to the font color and then only color would be seen. Field values can also be hex codes, RGBA values or HSLA values versus color names.

Now that we’ve covered the basics in conditional formatting, I recommend getting into Power BI and trying out conditional formatting for yourself. The best way to become comfortable doing something is to work with it using your data.

-Jerica Coleman, CRM and Power BI Consultant

If you’d like to explore Power BI for your business, contact us.

 

 

 

Power BI: Conditional Formatting

by

There are lots of formatting options within Power BI that really elevate reports. Conditional formatting can assist in quickly calling attention to potential problems with your data and is available for table and matrix visualizations. To access conditional formatting select the paint roller icon from the visualization tab when you have your table or matrix selected.

Depending on the column type you can format background and font colors, add data bars or icons and access a web URL using a color scale, rules or field value.

Today we are going to look at using color scale and rules to apply formatting.

To apply select a column to format and move the slider to on, this will show the advanced controls link. Select this to configure formatting.

All formatting options require you to select the field and summarization type for the conditions. This field does not need to be the same one being formatted and does not have to be on the table or matrix.

Color Scale Example

When formatting by color scale here are the things to consider:

  • Default formatting defines how blank cells will be treated.
  • Minimum and maximum values can be set using values from the selected field or by custom values.
  • The scale defaults to two colors. Select diverging to include a middle value color.

In our example we are formatting the background color of the sales territory column based on total sales (DOCAMNT). For blank cells default formatting has been set to treat as if their value is zero. Lowest and highest values are setting the scale and a diverging color was selected.
Here is what our table looks like.

Rule Example

Rules format based on cell values that can be greater than, equal to, or less than a value or if the cell is blank. Multiple rules can be combined, and the value can be a percent of the column values or a static number.

Rules cannot be based on the value of another field.

In our example we want to flag any project where the cost exceeded what was earned. A rule cannot be used to format if the contract earned is less than the actual cost directly, an additional step is needed.

First, we create a custom column subtracting the cost from the contract earned and any project where there was a cost overrun will be a negative value.

We are going to format the actual cost column based on the sum of our custom column. If the value is less than 0 the font will be red. Both the greater than and less than statements are required, so we set it as greater than 0 percent and less than or equal to the number 0. Here is how our table looks:

Multiple Rule Example

In this example we want to see which projects are performing poorly based on percent cost. Our custom column is going to divide cost by contract earned and give us a decimal number.

Using icon formatting we have set four categories. Numbers are used when we want to compare to the results in our custom column, and percent is used to fill out statements where we only have one value to use.

  • 40%-80% cost shows a green circle.
  • Over 80%, but less than 100% cost shows a yellow triangle.
  • Over 100% cost shows a red diamond.
    • Less than is set to 100 percent.
  • Less than 40% cost is flagged with a grey arrow.
    • Greater than is set to 0 percent.

Here is what our table looks like.

In our next post we will review adding data bars and URLs and formatting using field value.

-Jerica Coleman, CRM and Power BI Consultant

If you’d like to explore Power BI for your business, contact us.

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: Coming Clean

by

Once connected to data most people go right into trying to build their visualizations; however, they get frustrated because the data can be overwhelming and sometimes not even make sense.

Cleaning up your data can seem insignificant, but it is the most important thing you will do with your Power BI report. It serves three main purposes:

  1. Makes creating visualizations easier because you don’t have to filter through unnecessary data.

Raw tables from SQL or software systems can include anywhere from 50 to over 300 columns. A lot of these columns may be unnecessary for the reports you are looking to create.

  1. Simplifies end user experience.

Raw tables come over with schema names which may not make sense to end users. It is important to change the names of columns to match the verbiage used within your organization, so that when an end user searches or looks at data they understand what they are looking at.

  1. Improves report performance.

Pulling over 300+ columns and all the rows of data you have entered over the life of your system can really slow down report loading. Cleaning up your data by removing unnecessary columns and filtering down rows helps to speed up the loading when refreshing or updating your data.

Cleaning up data is done within the Power Query Editor which can be accessed two different ways.

When you first connect to your tables you can choose edit your table versus load it.

From the home tab, in the toolbar select Edit Queries.

The Power Query Editor has a lot of different features, in this post we are going to cover three of the features to help clean up most of your data.

Removing Unnecessary Columns

Columns can be removed one at a time or in bulk. To remove one column, either right click on the column itself and choose remove or with the column highlighted select remove columns from the home tab on the toolbar.

To remove multiple columns, you can use CTRL + Select to highlight multiple columns at once and remove as described above, or you can choose which columns to keep by selecting choose columns from the home tab on the toolbar. This will pop up a box that will allow you to uncheck the columns you want to remove.

Renaming Columns

This is a simple step, but so important to make sure your data reflects verbiage used in your organization. Columns can be renamed by either right clicking on the column and selecting rename or by selecting rename from the transform tab on the toolbar with the column highlighted.

Transform Column Data

Clean, consistent data is important to make sure your charts provide beneficial data. You can transform your data either by right clicking on the column and choosing transform or the options are available under the transform tab on the toolbar. There are different transformations available depending on the data type of the column.

Text Transformations:

Change the text within the cell to all lowercase, all uppercase or capitalize each word.

Trim or clean the text to remove extra spaces or non-printable text.

Length calculates the number of characters within a cell.

Date/Time Transformations:

Change to date or time only.

Pull out year, quarter, month, week or day data. This can be the actual time period or it can calculate the start or end of the time period.

For time you can pull out the hour, minute or second.

Number Transformations:

There are several number transformations available, such as rounding, absolute value, base-10, powers or standard mathematics.

If you make a mistake you can undo your editing under the applied steps section on the right hand side of the screen. To delete click the X next to the step. If you have chosen select columns you can click on the settings gear to the right of the step and it will bring the box back up allowing you to reselect the columns you want included.

Once you have completed all the edits close and apply the updates to your Power BI report.

These are just a few of the many functions available within the Power Query Editor, but they will make a big impact when using Power BI. We will dive into some of the other function in more details in future blog posts.

-Jerica Coleman, CRM and Power BI Consultant

Interested in learning more? Contact us!

Power BI: Let’s Get Connected

by

With over one hundred sources to connect to, Power BI has the capability to pull together a multitude of sources into one report.

Power BI Desktop

Power BI DesktopWithin the Power BI desktop select “Get Data” from the grey toolbar. A list of the most common data sources will appear and if you do not see your source type click on “More” to pull up the full list of data sources.

Depending on the type of source you may be prompted to enter a set of credentials, which can be your windows log-in or a specific organizational log-in.

 

 

 

Power BI Navigator

Once you have your data source selected and credentials entered, if required, a box will pop-up showing all the tables you can select from. If you select a table from the left side a preview will show up on the right allowing you to determine if you have the correct table. You can select multiple tables to load at one time; however, beware of bringing in too much data as it can take a long time to load.

After you have selected the tables you can load the data right away or choose to edit your query. If you are dealing with larger sets of data editing your query is the best option. The query editor loads a preview of your records and allows you to filter and change the query to bring over only what you need. Doing so will help report performance when loading and updating your data.

We will cover features of the query editor in a future blog post.

Power BI Online

Power BI OnlineConnect to data within the online version of Power BI by selecting “Get Data” located at the bottom of the black toolbar on the left-hand side of the page. The main screen will then allow you to select data connection types.

 

 

 

 

 

 

 

 

Power BI

My organization: If you have published content packs anyone within your organization would be able to connect to the data through this option.

Services: Power BI online allows you to choose from basic apps that connect to different types of online services, such as CRM or Power GP, which will pull in tables and a basic report.

Files: Pulls in from files that can be local or shared, such as from OneDrive or SharePoint.

Databases: Connects to databases and online systems you use. If using GP connecting through SQL is the recommended option.

Once connected to data you can begin making visualizations; however, if pulling in from databases or online systems it can be quite confusing at first. When pulling from system tables there can be anywhere from 10 to over 300 fields and the names do not always make sense. Generally, this is where people get frustrated and give up.

Over the next few blog posts we will be reviewing ways to clean up and prepare your data making it easier to work with when you start creating reports.

– Jerica Coleman, CRM and Power BI Consultant

Interested in learning more? 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.