Month: December 2016

Santa Loves Power BI and R
This past week I was talking with the big guy up north, jolly old fella, and the discussion came up about his toy production levels. Santa was complaining about how hard it was to measure the performance of all his elves. Naturally I started babbling about how much I enjoy Power BI and that I use it on all kinds of sources of data, google analytics, excel sheets, sharepoint, and SQL data warehouses just to name a few. Now by this point most people would have wandered off looking for another conversation, but I must have struck a chord with Santa. He jumped right in the conversation and told me how he had just moved all his local data centers into Azure and more specifically SQL data warehouses. It was saving him loads of money in addition it has freed up all his I.T. elves to move to more important tasks, building the NES Classic for Nintendo, they are way behind in production. To make a long story longer, I was able to convince Santa to give me a small sample of data so I could show him how to use R to visualize his data in PowerBI. Here is what I came up with:

Santa Production Levels

Needless to say he was very pleased. I explained the chart to Santa, each bar represents the average production volume for each elf. Then the whiskers at the end of the bar represent the +1 and -1 standard deviation away from that mean. It essentially tells you how consistent each elf is able to produce products and what is the average production rate. For example, Buddy the Elf can produce an average 148 items in a day, he has a daily variance of 10 items. Charlie can produce on average more items but has a wider daily variance. Snowflake has the lowest average production level but is one of the more consistent producers. Santa gave me a big smile and said “nice job.”

Let’s walk through how I did this.

Open up PowerBI Desktop, Click the Get Data button on the Home ribbon and select Blank Query. Click Connect to open the Query Editor. Click Advanced Editor on the View ribbon. While in the Advanced Editor paste the following code into the editor window, click Done to complete the data load.

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/12/Santa-Production.xlsx"), null, true),
    Production_Table = Source{[Item="Production",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Production_Table,{{"Elf", type text}, {"Toy", type text}, {"Prodution Volume", Int64.Type}})
in
    #"Changed Type"
```
Before you exit the query editor Rename the query to Production. It should look similar to the following:

Production Query

Click Close & Apply on the home ribbon.

Add the following measures by click on the New Measure button on the Home ribbon.
```
Avg = AVERAGE(Production[Prodution Volume])
```
The Avg measure will determine the height of each bar in the bar chart.
```
StdDev = STDEV.P('Production'[Prodution Volume])
```
The StdDev will calculate the standard deviation for each elf.
```
Ymax = [Avg]+ [StdDev]
```
The Ymax calculation adds the Avg measure value to the standard deviation for production. This produces the upper arm of the whisker.
```
Ymin = [Avg]-[StdDev]
```
The Ymin calculation is subtracts the standard deviation from the Avg measure value. This produces the lower arm of the whisker.

Once you have completed making all the measures you should have a Production table with the following fields:

Added Measures

Add the table visual by click on the Table visual in the Visualizations pane. Add the Fields which are shown below. Your table should look identical to this:

Production Table

Next, add the R Visual from the visualization Pane. When you click on this you will get a message stating “Enable Script Visuals” click Enable to proceed.

Note: If you have not installed R or enabled the preview features of R in Power BI you should follow this tutorial which helps you get everything set up. For this particular visual we are using ggplot2. This is a package for R and should be installed in the R environment. You can follow this tutorial on how to install ggplot2.

Add the following fields into the R visual:

Add Fields to R Visual

Next in the R Script Editor add the following code to generate the R Script.

library (ggplot2) # Load ggplot to run visuals

# Set up graph
ggplot(dataset, aes(x = Elf, y = Avg) ) +

# Insert the bar chart using acutal values passed to visual
# Stat = “identity” does not count items uses actual values
# set up transparency to 70% with Alpha
geom_bar( stat = “identity”, aes( alpha= 0.7, fill = Elf ) ) +
# draw the error bars, use pass Ymin & Ymax from PBI
geom_errorbar(aes(width = .5, colour = Elf , ymin = Ymin, ymax = Ymax)) +

# Change the Labels
labs(x = “Elf Name”, y = “Production Vol.” ) +

# Make the theme simple and remove gridlines
# Change the font size to 14
theme_classic( base_size = 18 ) +

# Remove the legend
theme( legend.position = “none”) +

# Change elements of the Axis, Angle, horizontal & Vertical position
theme( axis.text.x = element_text(angle = 90, hjust = 1, vjust = 0.3),
axis.text = element_text(colour = “black”),
axis.ticks = element_line(colour = “black”),
axis.title = element_text(colour = “black”),
plot.background = element_rect(colour = NA) )

Note: This code uses the R package ggplot2. It will error out if you don’t have ggplot2 installed.

Click the run icon to execute the R script.

Add R Script & Run Script

When the script runs you will have a beautiful production chart.

R Chart

Thanks for following along. Like always be sure to share if you liked this content. I am always, looking for feedback and possible topics so make sure you leave a comment below.

If you want to download a similar example already completed you can download this example from the R Script Showcase (don’t forget to give me a thumbs up).

Merry Christmas!
December 23, 2016
Measures – Year Over Year Percent Change
This tutorial is a variation on the month to month percent change tutorial. This specific exploration in year over year performance was born out of reviewing my google analytics information. The specific analysis question I am trying to answer is, how did this current month of website visitors compare to the same month last year. For example I want to compare the number of visitors for November 2016 to November 2015. Did I have more users this year in this month or last year? What was my percent changed between the two months?

Here is a sample of the analysis:

let’s begin with loading our data and data transformations. Open up PowerBI Desktop, Click the Get Data button on the Home ribbon and select Blank Query. Click Connect to open the Query Editor. Click Advanced Editor on the View ribbon. While in the Advanced Editor paste the following code into the editor window, click Done to complete the data load.

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 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VdDBDcQwCETRXnyOFMAYcC1W+m9jV8BhfH1ygJ9zBr/8CvEaz+DYNL7nDAFjnWkTTNsUbIqnLfyWa56BOXOagy2xtMB5Vjs2mPFOYwIkikIsWd6IKb7qxH5o+bBNwIwIk622OCanTd2YXPNUMNnqFwomp0XvDTAPw+Q2uZL7QL+SC1Wv5Dpx/lO+Hw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Start of Month" = _t, Sales = _t]),
 #"Changed Type" = Table.TransformColumnTypes(Source,{{"Start of Month", type date}, {"Sales", Int64.Type}}),
 #"Inserted Month" = Table.AddColumn(#"Changed Type", "Month", each Date.Month([Start of Month]), type number),
 #"Inserted Year" = Table.AddColumn(#"Inserted Month", "Year", each Date.Year([Start of Month]), type number)
in
 #"Inserted Year"
```
While still in the Query Editor rename the query to Data. Then click Close & Apply to complete the data load into the data model.

Load Monthly Data

Next, make four measures. On the Home ribbon click the New Measure button. Enter the following to establish a reference date to the subsequent equations:
```
Date Reference = DATE(2016,12,31)
```
Enter in the following equation to calculate the last year monthly sales amount.
```
LastYear = 
  VAR 
    CurrentDate = [Date Reference]
  RETURN
    CALCULATE( 
     SUM(Data[Sales]), 
     Data[Year] = YEAR(CurrentDate)-1
    )
```
Note: Using the NOW() function calls the current time when the query was last run. Thus, if you refresh your data next month the NOW() function wrapped in a YEAR() will return the current year from the date-time observed by PowerBI.

Following the same process enter the following additional measures. The ThisYear measure calculates the sales for the current month.
```
ThisYear = 
  VAR 
   CurrentDate = [Date Reference] 
  RETURN
   CALCULATE(SUM(Data[Sales]),Data[Year] = YEAR(CurrentDate))
```
Finally, add the calculation for the Year to Year comparison.
```
YoY Percent Change = DIVIDE([ThisYear], [LastYear], 0)-1
```
Since the YoY Percent Change is a real percentage we need to change the formatting to a percent. Click on the YoY Percent Change measure then on the Modeling ribbon click the % symbol in the formatting section of the ribbon.

Change Measure Format

Next, add a Stacked Column Chart with the following columns selected.

Add Stacked Column Chart

OK, we have a chart, but it is kinda awkward looking right now. The x-axis is the month number but we don’t have a month 0. That simply does not make sense. Let’s change some of the chart properties. While having the Stacked Column Chart selected click on the Paint Roller in the Visualizations pane. First, click on the X-Axis and change the Type to Categorical.

Change X-Axis

Then click on the Data Colors and turn on Diverging. Change the Minimum color to Red and the Maximum color to Green. Set the Center to a value of 0.

Change Colors

Click on the Title change it something meaningful, Center the text and increase the font size.

Change Title

Our bar chart looks much better. However, the month numbers do not look quite right. Visually the month indicators would be cleaner if we didn’t have any decimals. Click on the Month field and then on the Modeling ribbon change the Data Type to Whole Number. There will be a warning letting you know that you are changing the Data Type of the Whole number. Click OK to proceed with the change.

Change Month to Whole Number

Another successful percent change tutorial completed. I hope you enjoyed this year over year month comparison example. Make sure you share if you like what you see.
December 5, 2016

Month: December 2016

Santa Loves Power BI and R

Measures – Year Over Year Percent Change