Tag: R Scripts

Map with Data Labels in R
Mapping is one of the better features of PowerBI. It is one of the more distinguishing feature differences between Excel and PowerBI. You can produce a map inside an excel document using Bing maps, however, the experience has always felt a little like an after-thought. Mapping within PowerBI has a planned, and thoughtful integration. While the mapping functionalities within PowerBI Desktop are far improved when compared to excel, there are still some limitations to the mapping visuals. This past week I encountered such an example. We wanted to draw a map of the United States, add state name labels and some dimensional property like year over year percent change.

I started with the standard map visual, but this didn’t work because there is no ability to shade each state individually. This just looked like a bubbled mess.

Globe Map Visual

Next, I tried the Filled Map visual. While this mapping visual provides the colored states it lacks the ability to add data labels onto the map. Clicking on the map would filter down to the selected state, which could show a numerical value. Alternatively, you can place your mouse over a state and the resulting tag will show the details of the state (hovering example provided below).

Filled Map Visual

Still this did not quite meet my visual requirements. I finally decided to build the visual in R which provided the correct amount of flexibility. See below for final result. You can download the pbix file from the Microsoft R Script Showcase.

R Map Visual

In this visual, each state is shaded with a gradient color scale. The states with the lowest sales are grey and the states with higher sales numbers transition to dark blue. The darker the blue the more sales the state saw. Each state has an applied label. The color of the label denotes the percent change in sales. If the color is green then the sales this year were higher than last year, red means that the state sales were lower this year. The state name is listed in the label as well as the calculation for the year over year percent change.

Alright, let’s start the tutorial.

First, before we open PowerBI we need to load the appropriate packages for R. For this visual you will need to load both the maps and the ggplot2 packages from Microsoft R Open.

Open the R console and use the following code to install maps.
```
install.packages('maps')
```
Install Maps Package

Repeat this process for installing ggplot2.
```
install.packages('ggplot2')
```
After installing the R packages we are ready to work in PowerBI Desktop. First, we need to load our sample data. Open up PowerBI Desktop and start a blank query. On the View ribbon in the query editor open the Advanced Editor and enter the following M code.

Note: If you need some more help loading the data follow this tutorial about loading data using the Advanced Query Editor. This tutorial teaches you how to copy and paste M code into the Advanced Editor.
```
let
  Source = Excel.Workbook(Web.Contents("https://powerbitips03.blob.core.windows.net/blobpowerbitips03/wp-content/uploads/2016/10/State-Data.xlsx"), null, true),
  StateData_Table = Source{[Item="StateData",Kind="Table"]}[Data],
  #"Changed Type" = Table.TransformColumnTypes(StateData_Table,{{"StateName", type text}, {"Abb", type text}, {"TY Sales", Int64.Type}, {"state", type text}, {"Latitude", type number}, {"Longitude", type number}, {"LY Sales", Int64.Type}, {"Chng", type number}}),
  #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"TY Sales", "Sales"}})
in
  #"Renamed Columns"
```
After pasting the code into the Advanced Editor click Done to load the data. While in the Query Editor, rename the query to be StateData, then click Close & Apply on the Home ribbon.

Load Mapping Data

We still need to prepare the data further by adding two calculated columns. Click the bottom half of the New Measure button on the Home ribbon and select New Column.

Add New Column

Enter the following code into the formula bar that appears after clicking New Column.
```
Change = StateData[Abb] & " " & ROUND(100*StateData[Chng],0) & "%"
```
Change Column Measure

Again, click on the New Column button found on the Home ribbon and add the code for a color column.
```
Color = if(StateData[Chng] > 0 , "Dark Green", "Dark Red")
```
Color Column Measure

The Fields list should now look like the following.

Fields List

Add the R visual with the following fields.

R Visual Fields

Add the following R script into the R Script Editor.
```
# Load the ggplot2 and maps packages
 library(ggplot2)
 library(maps)

# Load the mapping data into a dataframe called states_map
 states_map <- map_data("state")

# Start ggplot2 by sending it the dataset and setting the map_id variable to state
 ggplot(dataset, aes(map_id = state)) +

# Add the map layer, define the map as our data frame defined earlier
 # as states_map, and define the fill for those states as the Sales data
 geom_map(map = states_map, aes(fill=Sales)) +

# Add the data for the labels
 # the aes defines the x and y cordinates for longitude and latitude
 # colour = white defines the text color of the labels
 # fill = dataset$Color defines the label color according to the column labeled Color
 # label = dataset$Change defines the text wording of the label
 # size = 3 defines the size of the label text
 geom_label( aes(x=Longitude, y=Latitude), 
  colour="white", 
  fill=dataset$Color, 
  label=dataset$Change, size=3
  ) +

# define the x and y limits for the map
 expand_limits(x = states_map$long, y = states_map$lat) +

# define the color gradient for the state images
 scale_fill_gradient( low = "dark grey", high = "#115a9e") +

# remove all x and y axis labels
 labs(x=NULL, y=NULL) +

# remove all grid lines
 theme_classic() +

# remove other elements of the graph
 theme(
  panel.border = element_blank(),
  panel.background = element_blank(),
  axis.ticks = element_blank(),
  axis.text = element_blank()
  )
```
After adding the R script press the execute button to reveal the map.

Paste R Script

Final Map Product

Notice how we have data included for Alaska and Hawaii but those states are not drawn. We want to remove the Alaska and Hawaii data points. Add the StateName field to the Page Level Filters and then click Select All. Now, un-check the boxes next to Alaska and Hawaii. The data is now clean and the map correctly displays only the continental United States.

Page Level Filters

Here is the filtered final map product.

Filtered Final Map

Thanks for following along. I hope you enjoyed this tutorial. Please share if you liked this content. See you next week.
October 10, 2016

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

Tag: R Scripts

Map with Data Labels in R

Digging Deeper with R Visuals for PowerBI