Month: October 2016

Pareto Charting in PowerBI
The Pareto chart is a handy visual, but is not so easy to build in either excel or PowerBI. In a Pareto chart, information is provided about an individual product or category as a bar, and a cumulative scale as a line which compairs all bars. This type of visual can be extremely helpful when conducting failure mode analysis, causes of a problem, or even product portfolio balances. For some more information on Pareto charts you can learn more here or here. If you’re interested in building a Pareto chart in excel, I have found this post from Excel Easy to be helpful.

To give you a little teaser of what we will be building today, below you will see an image of the final Pareto chart. On the left side we have sales of units, and on the right is the cumulative percent of all sales. Using the Pareto chart a user has the ability to see which products comprise the majority of your sales. For example, the first 4 bars total approximately 50% of all sales.

Pareto Final Product

Alright, let’s get started.

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.

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/Sample-Data.xlsx"), null, true),
 Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
 #"Changed Type" = Table.TransformColumnTypes(Table1_Table,{{"Item", type text}, {"Sales", Int64.Type}, {"Segment", type text}})
in
 #"Changed Type"
```
Rename the Query to Data. Once you’ve completed the data load your data should look like the following.

Load Data to Query Editor

On the Home ribbon click Close & Apply to complete the data load.

Close and Apply

Let’s begin with a little exploration of our data.

Pro Tip: When I am building reports I often load the data and then immediately start building a couple of tables and slicers. It helps me understand how my data reacts to the slicers and helps me determine how to shape the data so that the visuals will work properly. For this example, we only have one table, but when loading data things can get rather complex due to loading multiple tables with multiple relationships.

Add a Slicer for the Segment. Enhance the look of the slicer by changing it from a vertical to a horizontal slicer. While the slicer is highlighted, click the Paint Roller expand the General section and change the orientation from vertical to Horizontal.

Segment Slicer

Repeat the same process to add a Slicer for the item field.

Item Slicer

Next, add a table view of all the fields. Start with Segment, then Item and finally add Sales to the Table Visual.

Data Table

Notice, now that we added all the Fields, there are a number of repeating values. We have Category 1 and Item 1 repeated 9 times. In some cases, it will be necessary to have this level of data brought into the data model within PowerBI. A common reason is that this level of granularity is required for other report pages, or visuals. It is OK to bring large amounts of data, but as a method of best practice it is recommended that you bring in the data required to support the visuals.

Now, to address these multiple items that we see in our data. In the sample Pareto image provided at the beginning of this Tutorial we only had one bar for Category 2 Item 3. Thus, we need to summarize each grouping of every Category and Item combination. To do this we will construct a summary table.

First, we will create a unique Key that will be used to summarize each combination of Category and Item pair. Click the bottom half of the New Measure button located on the Home ribbon.

Calculated Column

Enter the following DAX expression. This new column titled Blend will be the unique Key that is utilized to summarize the data.
```
Blend = Data[Segment]  &  "-"  &  Data[Item]
```
Select the Modeling ribbon and then click on the New Table button. Enter the following DAX expression.
```
Summary = SUMMARIZE('Data', Data[Blend], "Sum Sales", SUM(Data[Sales]) )
```
For more information on the SUMMARIZE function you can visit the Mircosoft Summarize documentation page. In this equation we first select the table and in this case it is ‘Data’. Then the column we want to summarize or group by is the Segment column noted as Data[Blend]. The next field is the title of the summarized field column, noted as “Sum Sales”. Then DAX function that calculates the Sum of the column labeled Data[Sales], noted as SUM(Data[Sales]). It is relevant to point out here that the SUMMARIZE function will only work with building a new table and not as a calculated column or measure.

Add a new Table visual to the report and include the two newly created fields from the Summary table.

Summary Table Visual

We have a field titled Blend which is our Key for all the summarized groupings. Next, we will want to parse out the Segments and Items from this blend column. We will want to use Category 1 & 2 in a slicer and the same for Items 1 to 5. Highlight the summary table by clicking the grey space next to the word Summary. Click the New Column button on the Modeling ribbon and enter the following DAX expression.
```
Segment = PATHITEM(
   SUBSTITUTE(Summary[Blend], "-" , "|" ),
   1 )
```
In this expression the Substitute function replaced the dash “-” with a “|” character. Then the PATHITEM function can then parse the text into segments. By entering a 1 we select the first item in the sequence. For our example we only have two items, but when you’re working with file paths you can have multiple items in the path such as “\users\mike\my documents\my folder\”, which would equate to users = position 1, mike = position 2, my documents = position 3, etc..

Add another new column with the following DAX expression for the item column.
```
Item = PATHITEM( 
  SUBSTITUTE(Summary[Blend], "-" , "|" ),
  2 )
```
Note: We changed the PATHITEM position from 1 to 2.

Next add the newly created Segment and Item columns to our summary table visual that we created earlier.

Add New Fields

Nice job so far. Now we have to modify our slicers to point to the new Item and Segment fields we created in the Summary table. Select the Segment Slicer Visual and add the Segment Field from the Summary table.

Update Segment Slicer

Update Item Slicer

Now that we have updated the slicers, we can now can control the table visual made from the Summary table.

Select Category 1 and Items 1 to 3

Pro Tip: To select multiple items in a slicer you can hold down the Ctrl button on the key board and click multiple slicer items. This is how I was able to select Items 1 to 3.

Now we are ready to build the measures that will support the Pareto chart. Click on the bottom half of the New Measure button on the Home ribbon and select New Column. Add the following DAX expression to rank all the items in the Summary table.
```
Ranking = RANKX(  'Summary',   'Summary'[Sum Sales])
```
Add a measure for the Cumulative total according to the new ranking column we created. Click the top half of the New Measure button on the Home ribbon. Add the following DAX expression.
```
Cumulative Total = CALCULATE(
    SUM( Summary[Sum Sales] ),
    FILTER( ALLSELECTED( Summary ),
        Summary[Ranking] <= MAX( Summary[Ranking] )
    ))
```
Repeat the add measure process and add a Total measure which will total only the items from the summary table that have been selected in the report view. Add the following DAX expression.
```
Total Sales = CALCULATE(
 SUM( Summary[Sum Sales] ) ,
 ALLSELECTED( Summary )
 )
```
For the last measure, repeat the process to add another measure. Enter the following DAX expression as a measure.
```
Cumulative Percent = [Cumulative Total] / [Total Sales]
```
The Cumulative Percent measure is a calculated as a percentage, thus we need to change this measure’s formatting to percentage. Click the measure labeled Cumulative Percent then change the Format to Percentage which is found on the Modeling ribbon.

Change Formatting

Your Summary table should now look like the following.

Updated Fields List

To see all the calculations that we just created add all the fields from the Summary table to the Summary table visual we created earlier.

Full Summary Table Visual

At last, we are ready to add the Pareto chart. Add the following fields to the line and stacked column chart.

Add Line and Stacked Bar Chart

Order the data in descending order by the number of sales by click the visual’s Ellipsis and selecting Sort By Sum Sales.

Sort by Sales

This changes the order of the items to make a Pareto chart.

Final Pareto Chart

Thanks for following along. Share if you enjoyed this tutorial.
October 17, 2016
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

Fixing Measure Madness

Often times when you’re working with large data models you will have multiple tables with many relationships. It could be complex maybe you’ve seen something like the following:

Once all the tables have been loaded the manic measure building begins to support all the different visuals. A couple of sums here, a number of calculates over there, and boom, a beautiful report. You stand back and survey the work and realize you’ve built measures all over the place, in different tables, maybe even stuck a couple of measures in the wrong place. Whoops.

Maybe we should think about cleaning things up a bit, if only there was a way to group the measures. How do I group my measures? I’m glad you asked. With a little trickery we can make a measure table. Let’s begin.

First we will load a little data. For this tutorial we will simply copy and paste in some data.

Note: For the full tutorial on manually entering in data visit this page.

On the Home ribbon click the Enter Data button. Copy in the table below into the Create Table window. Rename the table Sales Data and click Load to exit.

Salesman	Item	Unit Sales	Revenue
Salesman 3	Item 4	405	1357
Salesman 1	Item 3	339	1649
Salesman 1	Item 3	315	1332
Salesman 3	Item 3	418	1531
Salesman 1	Item 3	482	1633
Salesman 2	Item 4	448	1676
Salesman 1	Item 4	391	1432
Salesman 2	Item 1	341	1539
Salesman 3	Item 1	419	1482
Salesman 2	Item 4	414	1610
Salesman 1	Item 4	351	1670
Salesman 3	Item 3	449	1795

Upon loading our data table we now have the following fields.

Now, let’s make a measure that calculates the revenue per unit. On the Home ribbon click the New Measure button and enter the following DAX measure.

Revenue Per Unit = SUM('Sales Data'[Revenue]) / SUM('Sales Data'[Unit Sales])

Next, make a table with the following fields.

Great! but, as we all know this is how the measure madness begins. From here we refine and finesse the data to craft the data story, and end up with tons of additional tables and measures.

Pro Tip: You can use the search window at the top of the Fields window to help you find buried measures or fields of data.

Let’s make the measure table. Start by clicking Enter Data on the Home ribbon. Rename the new table to My Calcs, and rename Column1 to Calcs. You don’t have to re-name column1, but since I’m OCD about my data I like to rename the column to the same name as the table. Then click Load to exit the screen.

We now have a new table labeled My Calcs with one column labeled Calcs. Next highlight the measure we created Revenue Per Unit. Then on the Modeling ribbon change the home table from Sales Data to My Calcs. This will move the measure.

Right click on the Calcs column in the My Calcs table and then select Hide.

Next Save and then reopen the document (it’s a Microsoft thing I guess). After the document has reopened the My Calcs table has changed it’s icon from a table to a Measure icon.

For kicks and giggles add the following measure to the My Calcs table.

Total Revenue = SUM('Sales Data'[Revenue])

Ok, one more.

Total Unit Sales = SUM('Sales Data'[Unit Sales])

There you go. A very straight forward approach to cleaning up all the random measures in your data model. I have found that when others team members are working with your data model this helps other people understand which fields have been calculate and which ones were imported via a query. This also helps you group logical calculations, further creating clarity within your data model.

If you want to read up more on making measure tables check out this great site (also linked below). In addition to walking you through creating a measure table it also explains how to make a measure table when using direct query mode. As the article explains, while you are in direct query mode you are unable to manually enter data. Nice job, Soheil Bakhshi, well done.

How to Define A Measure Table in Power BI Desktop

If you want to take your DAX skills to the next level, try jumping into this book by Rob Collie and Avichal Singh. It’s an easy read but very insightful.

If you liked this tutorial make sure you share. See you next week!

October 4, 2016

Intro to Guy In a Cube

Kicking off my video series for PowerBI.Tips I have to give incredible props to Adam Saxton. Adam is a Microsoft Employee who creates THE BEST content for PowerBI. There are a lot of videos out on the internet that are interesting and helpful to learn from. However, Adam takes it to another level. The videos are relatively short and packed with useful information. Below is the introduction video to Adam who is The Guy in a Cube.

I will be definitely sharing more videos from Adam in the future!

October 3, 2016

Month: October 2016

Pareto Charting in PowerBI

Map with Data Labels in R

Fixing Measure Madness

Intro to Guy In a Cube