Category: Report Visualization Tips

Digging Deeper with R Visuals for PowerBI

Back by popular demand, we have another great tutorial on using R visuals. There are a number of amazing visuals that have been supplied with the PowerBI desktop tool. However, there are some limitations. For example you can’t merge a scatter plot with a bar chart or with a area chart. In some cases it may be applicable to display one graph with multiple plot types. Now, to be fair Power BI desktop does supply you with a bar chart and line chart, Kudos Microsoft, #Winning…. but, I want more.

This brings me to the need to learn R Visuals in PowerBI. I’ve been interested in learning R and working on understanding how to leverage the drawing capabilities of R inside PowerBI. Microsoft recently deployed the R Script Showcase, which has excellent examples of R scripts. I took it upon myself to start learning. Here is what I came up with.

This is an area plot in the background, a bar chart as a middle layer and dots for each bar. The use case for this type of plot would be to plot sales by item number, sales are in the dark blue bars, and the price is shown as the light blue dots. The area behind the bars represent a running total of all sales for all items. Thus, when you reach item number 10, the area represents 100% of all sales for all items listed.

If you want to download my R visual script included in the sample pbix file you can do so here.

Great, lets start the tutorial.

First you will need to make sure you have installed R on your computer. To see how to do this you can follow my earlier post about installing R from Microsoft Open R project. Once you’ve installed R open up the R console and enter the following code to install the ggplot2 package.

install.packages("ggplot2")

Once complete you can close the R console and enter PowerBI Desktop. First, we will acquire some data to work with. Click on the Home ribbon and then select Enter Data. You will be presented with the Create Table dialog box. Copy and paste the following table of information into the dialog box.

Item	Sales	Price	Customer
1	100	20	Customer A
2	75	25	Customer A
3	20	30	Customer A
4	18	15	Customer A
5	34	26	Customer A
6	12	23	Customer A
7	20	22	Customer A
8	15	19	Customer A
9	10	17	Customer A
10	8	26	Customer A
1	120	21	Customer B
2	80	24	Customer B
3	62	33	Customer B
4	10	15	Customer B
5	12	26	Customer B
6	60	24	Customer B
7	20	23	Customer B
8	10	20	Customer B
9	8	16	Customer B
10	7	20	Customer B

Rename your table to be titled Data Sample.

Click Load to bring in the data into PowerBI.

Next, we will need to create a cumulative calculated column measure using DAX. On the home ribbon click the New Measure button and enter the following DAX expression.

Cumulative = CALCULATE(  sum('Data Sample'[Sales] ) ,   FILTERS(  'Data Sample'[Customer] ) ,  FILTER( all( 'Data Sample' )  ,  'Data Sample'[Item] <= MAX( 'Data Sample'[Item] ) ) )

This creates column value that adds all the sales of the items below the selected row. For example if I’m calculating the cumulative total for item three, the sum() will add every item that is three and lower.

Now, add the R visual by clicking on the R icon in the Visualizations window.

Note: There will be an approval window that will require you to enable the R script visuals. Click Enable to proceed.

While selecting the R visual add the following columns to the Values field in the Visualization window.

Note: After you add the columns to the Values the R visual renders a blank image. Additionally, there is automatic comments entered into the R Script Editor (the # sign is a designation that denotes a text phrase).

Next, enter the following R code into the script editor.

library(ggplot2)   # include this package to use Graphing functions below

ggplot(dataset, aes(xmin=1, x=Item)) +    # Initialize ggplot function, define the x axis with Item data
 geom_ribbon(fill=c("#D7DDE2"),           # Set the color of the Area Plot,
 aes( ymin=0, ymax=Cumulative )) +        # Define the Y-Axis data
 geom_bar(fill=c("#21406D") ,             # Define the color of the Bars
 stat = "identity" ,      # Define the Statatics property of the bars - This is a required field
 width=.6 ,               # Change the bar width to 60% - 1 would be full bar width
 aes( x=Item, y=Sales )) +          # Define the X and Y axis for bars
 geom_point( color=c("#809FFF"),    # Define the color of the dots
 size=4,                  # Define the dot size
 aes( x=Item, y=Price )) +          # Define the X and Y axis values
 theme_classic(base_size=18) +      # Remove unwanted items from plot area such as grid lines and X and Y axis lines, Change font size to 18
 theme( axis.title.x = element_text(colour = "dark grey"),     # Define the X axis text color
 axis.title.y = element_text(colour = "dark grey")) +          # Define the Y axis text color
 labs( x="Item Number", y="Sales")                             # Define the labels of the X and Y Axis

Press the execute R Script button which is located on the right side of the R Script Editor bar.

The R Script will execute and the plot will be generated.

Great, we have completed a R visual. So what, why is this such a big deal. Well, it is because the R Script will execute every time a filter is applied or changed. Lets see it in action.

Add a slicer with the Customer column.

Notice when you select the different customers, either A or B the R script Visual will change to reflect the selected customer.

Now you can write the R script code once and use the filtering that is native in PowerBI to quickly change the data frame supporting the R Visuals.

As always, thanks for following along. Don’t forget to share if you liked this tutorial.

Want to learn more about PowerBI and Using DAX. Check out this great book from Rob Collie talking the power of DAX. The book covers topics applicable for both PowerBI and Power Pivot inside excel. I’ve personally read it and Rob has a great way of interjecting some fun humor while teaching you the essentials of DAX.

September 21, 2016

Using R Visuals in Power BI
For those of you who have been hanging around PowerBI for a while you have likely heard about integration with R visuals. No, this isn’t a twisted dream where Power BI now ships with Pirates… Rather, this has been a highly untapped feature.

In a brief summary R or as it is known on its site R Project for Statistical Computing, is a statistical open source software package that enables mathematicians, statisticians, or data scientists to quickly calculate complex analysis. It is the tool of us super nerds. Now R by it’s self isn’t super powerful, it’s the numerous packages that have been developed by people way smarter than me that can do very amazing functions. Packages include functions for forecasting, math functions, statistic functions and best of all charting functions. Well, this may be fine and dandy so what? Well here is the best part. Microsoft has chosen to integrate and support various releases of R into it’s tools. For example R can now be leveraged within SQL server 2016, and now visuals built in R can be leveraged in Power BI Desktop and PowerBI.com. R can also be used to transform and prepare data during a date set load.

The important note here is that Microsoft has released it’s own open version of R. This distribution is called MRAN, and can be found at this site. The MRAN has been slightly tweaked from the R Project. In the Microsoft version of R, (which I will refer to as MRAN) there has been stability fixes and the improved performance (added Multi threaded Performance).

So enough back ground lets fire this thing up.

First you will need to install the latest version of MRAN.

Navigate to the following address https://mran.microsoft.com/ Click the Download button found at the top middle of the page.

mran download page

Note: At the time of this Tutorial the current version of MRAN is 3.3.1, it is likely that this will change since Microsoft is constantly updating this site and releasing new stabilized & enhanced performance versions of R.

Select the platform that you will be using to install MRAN on. I’m using windows, thus I’ll be downloading and installing the top installation version.

Windows Platform of MRAN

Note: If you need additional installation help you can follow / read the documentation provided by Microsoft. It can be found here.

In order to keep this tutorial brief I will assume you know how to install software and have made it through the MRAN installation successfully. Once installed you should have the following program installed in your start menu.

Installation of R

Run the new installation of R. The R installation will open up a console window.

R Console

At the bottom of the console window is a red line where you enter commands. Enter the following code and press enter.

install.packages(“corrplot”)

This will install the proper R package that we will use later in PowerBI. After running this line of code the console will download the correct package and install it on your computer.

Install corrplot Function

At this time you can close the R console program.

Now, open up PowerBI Desktop. Once in PowerBI desktop click on the File Button at the top left hand part of the screen. Next, Click Options and Settings.

PowerBI Options and Settings

Then click on the Options button.

Options Button

Under the Global options menu on the left verify that your new installation of MRAN is listed. PowerBI should automatically detect the installation and show the installation with the current version number in the home directory:

R Home Directory

Seeing the listed installation in the Home Directory verifies that R has been properly installed on your computer. Clicking OK will close the window.

Data Time!! Below is the M Language that can be used in your Query Editor. Copy the code below and enter it into the Advanced Editor found in the Query Editor.
```
let
 Source = Excel.Workbook(Web.Contents("https://powerbitips03.blob.core.windows.net/blobpowerbitips03/wp-content/uploads/2016/09/CarDetails.xlsx"), null, true),
 CarData_Table = Source{[Item="CarData",Kind="Table"]}[Data],
 #"Changed Type" = Table.TransformColumnTypes(CarData_Table,{{"Year", Int64.Type}, {"Make", type text}, {"Model", type text}, {"Liters", type number}, {"Hp", Int64.Type}, {"Cylinders", Int64.Type}, {"MPG City", Int64.Type}, {"MPG Hwy", Int64.Type}})
in
 #"Changed Type"
```
Note: If you want to learn how to enter M language code into the Query Editor follow this Tutorial.

Once you have pasted the code above into the Query Editor it should look like the following:

Advanced Editor

Clicking Done will close the Advanced Editor and you will have data loaded into the Query Editor. You must have an internet connection to connect to this data. Rename your query to Car Data. Then on the Home ribbon click Close & Apply to load the data into the data model.

Car Data in Query Editor

Generate a simple table visual to see our data in table form:

Table Visual

Add an R visual by clicking the R inside the Visualizations bar. When you click on the R visual you will see a pop-up, click Enable to proceed.

Enable R Visuals

Doing this will open up a visual pane on the page and reveal an R script editor at the bottom of the page window.

R Script Editor

While keeping the R visual selected add the following fields to the visual under the Values field:

Add Columns to R Visual

After adding these fields the R Script Editor will update and reveal code which informs you that your data from the selected columns will be added to a dataset.

R Code Script Editor

Next add the following code into the white area below the #dataset <- unique(dataset) statement.

require(“corrplot”)
library(corrplot)

M <- cor(dataset)

corrplot(M, method = “circle”, tl.cex=0.6, tl.srt = 45, tl.col = “black”, type= “upper”, order=”hclust”)

This loads a package called corrplot which allows you to apply a graph that has a correlation plot between metrics. The M <- cor(dataset), takes your data runs a function called cor and then saves the results into a new variable called M.

Next click the Play button icon found on the right of the grey bar on the R Script Editor.

Running the R Script

Success! You have completed a correlation plot using R within PowerBI. Nice job.

Final Plot

Bonus:

If you want to get fancy with this correlation plot you can change the circles to the actual correlation values. Change the last line of the R Script Editor code to the following and press the run script button:

corrplot(M, method = “number”, tl.cex=0.6, tl.srt = 45, tl.col = “black”, type= “upper”, order=”hclust”)

This removes the circles and then populates the correlation plot with numerical values representing the correlation between the various data features.

Correlation Numbers

The blue numbers represent values that have a positive correlation, while the red numbers represent a negative correlation. In practical terms the higher the Horsepower (HP) of the vehicle the lower the Miles per Gallon (MPG) that are realized.
September 15, 2016
Power BI Histogram with Bins – Includes DAX
Alright to start this Tutorial off right we are going to incorporate the new feature released this spring from Power BI, called publish to web. Below you can view last weeks tutorial and interact with the data. Feel free to click around to see how the visualization works (you can click the shaded states or on the state names at the bottom.

For this tutorial we will build upon the last tutorial, From Wikipedia to Colorful Map. If you want to follow along in this tutorial click on the link and complete the previous tutorial.

Materials:
- Power BI Desktop (I’m using the March 2016 version, 2.33.4337.281) download the latest version from Microsoft Here.
- Mapping PBIX file from last tutorial download Maps Tutorial to get a jump start.
Picking up where we left off we have data by state with data from the 2010 Census and 2015 Census.

Data from Region Maps Tutorial

What we would like to identify is how many states are within a given population range. Say I wanted to see on the map, or in a table all the states that had 4 million or less in population in 2010.

To do this we will create bins for our data. Enter custom data in this format. For the tutorial on entering custom data into Power BI Desktop check out this tutorial on Manually Enter Data. Click on the Enter Data button on the Home ribbon. Enter the data as following:

Enter Bucket Data

Note: Make sure you name the new table Buckets as shown in the image above.

Click Load to bring the data into the data model. Notice we now have a new table in the Field column on the right.

Buckets Table

Next we will create a measure to evaluate the state level data into our newly created buckets. This will be produced using DAX (Data Analysis Expressions). DAX is an extremely powerful language which is used in SQL applications and Analysis Services. More information can be found on DAX here. Since DAX is so complex we won’t go into a full explanation here. However, we will have many more topics in the future working on and building DAX equations.

Click the Ellipsis next to the table labeled US Census. Then click the first item in the list labeled New Measure.

Note: Ellipsis is the term used for those triple dots found in newer Microsoft applications.

Example of Ellipsis

A formula bar opens up underneath the ribbons bar. Here is where we will name and type in the new measure. The equation we will need to add is the following.
```
Bins = CALCULATE(COUNTROWS(FILTER('US Census',and([2010 Census] >= min(Buckets[Min]),[2010 Census] <= MAX(Buckets[Max])))))
```
Press Enter to enter the measure into PowerBI.

Explanation of Equation: All text before the equal sign is the name of the measure. All the data behind the equal sign is the DAX expression. Essentially this equation is calculating the number of rows where we have data between the Buckets “Min” value and Buckets “Max” value. This is the magic that is DAX. In this simple expression we can compare all our data against our buckets ranges we made earlier.

Finally our new Bin measure should look like the following.

Bin Measure Created

Now lets modify our visuals to incorporate the new Bins measure. Click on the existing map on the page. Remove the % Change item from the Values selection. Add the Bins Measure to the Values section. Notice the map changes color. Next, add the Name field from the table called Buckets into the Legend field. Our map should look similar to the following:

Map with Bins Added

Next Click on State, 2010 Census, Bins, and Name (from Buckets table) and make a table. It should look like the following:

Table of Bins Measure

Lastly, we will build a bar chart using our Bins Measure. Click on the Stacked Column Chart Visual and add the following items to the corresponding categories: Axis = Name (from the Buckets table), Legend = Name, and Value = Bins (from US Census table). This will yield the following visual.

Bins in Bar Chart

Click on the Ellipsis of the bar chart and then click Sort By, finally click Bins. This will order the items in descending order by the count of the items found in each bin.

Now have fun with your new data. Click on each of the bars in the bar chart and watch your data transform between the table, and the map.

Selection Big in the Bar Chart

Here is the final product if you want to engage with the data.

I have to give credit where credit is due. Below is the page from Power Pivot Pro that I used to create binning in the tutorial chart. The binning shown on PowerPivotPro is for Power Pivot but the functionality is the same. Enjoy.

http://www.powerpivotpro.com/2015/03/creating-a-histogram-with-a-user-defined-number-of-buckets/

Want to learn more about PowerBI and Using DAX. Check out this great book from Rob Collie talking the power of DAX. The book covers topics applicable for both PowerBI and Power Pivot inside excel. I’ve personally read it and Rob has a great way of interjecting some fun humor while teaching you the essentials of DAX.
April 27, 2016
From Wikipedia to a Colorful Map
For this tutorial we are going to get some real data from the web. One of the easiest sources to acquire information from is Wikipedia. I will caveat this by saying, it is easy to get data from Wikipedia, but I don’t know if you can always trust the reliability. That being said, we are going to acquire the U.S. population and growth rate from 2010 to 2015 from the Wikipedia Web page.

Materials:
- Power BI Desktop (I’m using the March 2016 version, 2.33.4337.281) download the latest version from Microsoft Here.
- Link to the data from Wikipedia, Here. ( https://en.wikipedia.org/wiki/List_of_U.S._states_by_population_growth_rate )
Let’s begin.

Open up Power BI Desktop. Click on the Get Data button. On the left of the Get Data menu click Other then select the first item titled Web. Click Connect to continue.

Get Data from Web

In the From Web window enter in the following web address. You can copy and paste it from below.
```
https://en.wikipedia.org/wiki/List_of_U.S._states_by_population_growth_rate
```
Click OK to move to the next menu. After a bit of thinking the Power BI will present the Navigator window. This is what Power BI has found at that specific web address. On the left side of the screen there is a folder. This is the web page folder location that we loaded earlier. Power BI then intelligently looks through the website code for tables it can distinguish. By clicking on each table you can see a preview of the data returned on the right side of the window.

Try clicking on the various tables such as Document, External links, or Table 0. For our example lets click on Table 0. Click on the button at the right hand corner labeled Edit. We are going to slightly modify this data before we load it to the data model.

Navigator Window

You’ll notice once we load the data there are some items we’d like to remove. In row #2 the label is District of Columbia, which technically isn’t a state. Also further down we see in row #25, the entire U.S. population is shown. Again, we don’t want these values to show, we only want the 50 states. To remove this data we will use a text filter to remove any item in the Rank column that has a “–” (which is called an em-dash, see note below for more details on how to select this text character).

Note: There are two kinds of dashes that your computer uses. One is called the en-dash(-), the second being the em-dash(–). It is very hard to distinguish the difference between the two dashes. The image below shows a better contrast when used in Microsoft Word.

Em-Dash vs. En-Dash

The en-dash is shorter than the Em-dash. The Key for the en-dash is next to the number 0 on your keyboard. To select the em-dash you need to use a bit of Microsoft trickery. The Em-dash will be presented when you hold the Alt key and type 0151 on a keypad. This selects the specific ASCII character for the em-dash. For more information on selecting the em-dash visit here.

Click the drown down button in the column labeled Rank. Select the item labeled Text Filters, and then Click Does Not Contain…

Text Filter on Rank Column

Enter in the em-dash code by using Alt 0151 to enter in the correct dash into the Filter Rows dialog box. Click OK to proceed.

Enter EM-Dash in Filter Rows Dialog

If we entered the correct em-dash we will now be presented with a cleaned list of U.S. states with only numbered items in the Rank column.

Next we will clean up the query slightly to make it easier to deal with. Delete the column labeled Rank, and Change. Rename the query to something a little more meaning full such as US Census.

Remove Columns & Rename Query

Note: You can delete a column by pressing the Remove Columns button on the Home ribbon. A second method is to right click with your mouse on the column you want to remove and selecting Remove.

Next we will add our own calculated column which will calculate the 2010 to 2015 percent change. Click the ribbon labeled Add Column and select the first icon on the far left labeled Add Custom Column. The Add Custom Column dialog box will open. Enter the name for the new column, then by clicking on the columns in the available columns on the right you can build an equation. For this example we are using the percent change calculation which is the following:
```
Percent Change = [New Value / Old Value ]- 1
```
Using the columns we imported from Wikipedia we will have the following equation:
```
= [2015 estimate] / [2010 Census] - 1

Update: this formula has now changed to 2016 estimate as time has progressed since this first tutorial was posted.
The new column should have this following formula: = [2016 estimate] / [2010 Census] - 1
```
This inserts a new column with the calculated percent change between the 2010 census and the 2015 census. Click OK to proceed.

Add Custom Column

Finally we want to change the type of data in the % Change column so our data model will operate as expected when producing visuals. Click the Home ribbon, then click the % Change column. Change the Data Type: from Any to Decimal Number. This informs the data Model how to treat the data held the % Change column. We are finished data modeling and now click Close & Apply on the Home ribbon.

Now we have all our data loaded into the data model ready to build a map.

Click the Column labeled State and then click % Change. This yields a map with circles on it. Change the visual to a filled map by selecting a different visual, the Filled Map icon (circled in red below). Doing so produces a shaded map of the US, where each state is colored according to the % Change.

Filled Map Selection

Finally lets add some color to the data. Click the visual’s Format properties (the little paint brush in the visuals window). Expand the Data Colors section by clicking on the title Data colors. Diverging is set to off. Change it to On. Change the Minimum color to Green, the Center color to Yellow, and the Maximum color to Red.

Colored Map

The states with the largest population change are in Red, while all the states with the smallest population change.

Please share if you liked this tutorial. Thanks.
April 20, 2016
Map it, Map it Real Good
This tutorial is a real simple mapping exercise. I was talking with a colleague today about Power BI and I was challenged to map something using latitude and longitude. I had played with mapping before but not using latitude or longitude.

I’d have to say if you want to impress someone with your PowerBI skills adding a map is a good way to do so. Typically this a functionality that you can’t add into excel, well at least not with out some serious effort.

Alright, here we go..

Resources for this project are:
- Power BI Desktop (I’m using the March 2016 version, 2.33.4337.281) download the latest version from Microsoft Here.
- Excel file with a table in it with our location information that can be downloaded here: Locations Data Set
After downloading the Locations Data Set, Open up PowerBI and load the Excel file into Power BI. If you need to learn how to load Excel files you can follow the loading excel tutorial.

Click the Get Data on the Home ribbon. Select the first option Excel and click Connect at the bottom of the Get Data window.

Navigate to the downloaded file called Locations.xlsx and open the file by clicking Open in the bottom right hand corner.

Next, the navigator window will open. Select the table (denoted with the grid with a blue top header) called Locations. Then Click Load to load the data into the data model.

Navigator Window Selection

Note: there are two different icons in the Navigator window. One is called Locations which is a Table within the Excel document. While the other is called Sheet1, which is simply the first sheet in the excel workbook. For Future references it is much easier to make tables in excel and use them to load data in to PowerBI than using just a worksheet. So whenever possible try to form your data in Excel into Tables. When loading a table the headers of the table automatically load into the column names in the PowerBI data models.

We now have loaded the data into a new Table in PowerBI called Locations.

To make the map check the boxes for Latitude and Longitude. Power BI intelligently understands that latitude and Longitude are mapping functions and we are now presented with a map with tiny blue dots.

Map from our Data

Lets add some more data to enhance the map. We can change the size of the circles at each location by dragging the column called Attenders over to the Values field for this visual.

Change Bubble Size

We have now changed the size of the circles relative to each other to show the number of people that we saw at each location. To add color to the map drag the column called Event to the Legend option of the visual. This yields a map that now has each circle with a different color according to the event name.

Colored Bubbles on a Map

To enhance our visual further we will add a bar chart with the total count of attenders per event. To do this click any where on the visual page (this will de-select the map visual on the page). Now click the Event column and then the Attenders column. This will present you with a table list of events and the corresponding attendees. Leaving the table visual highlighted click the Stacked Bar Chart which is in the upper left hand corner of the Visualizations window.

Adding a Bar Chart

I circled the triple dots on the bar chart. Click the triple dots and a menu will appear. First click Sort By, then click Attenders. This will sort the attenders in descending order from the largest amount at Kohl’s Corp. down to Harley Davidson. Drag the column labeled Event to the visualization option called Legend. This colors the bar chart.

Colored Bar Chart

Note: The colors in the bar chart match the colors in the map we made earlier. This build uniformity in your reports and when your filtering items colors across visuals make sense.

Take some time to click on each of the bars on the bar chart. Notice how the map re-draws with only the data for that selected item. To select multiple bars on the bar chart hold the CTRL button and click on the multiple bars.

Nice job. We have finished the mapping tutorial. Share if you liked it below.
April 14, 2016