Tag: Beginner

Measures – Intelligent Card Visual – Using DAX
As I have been exploring PowerBI and building dashboards I have noticed that often the visuals can obscure your data. As you click on different visuals there is a need to highlight different pieces of data. Take for example the following dashboard:

Sample Visual Example

Notice the different car types in the bar chart. As you click on each vehicle type, Diesel, Hatchback, etc.. you expect the data to change accordingly. In some cases it is helpful to present a card visual to show the user what you selected and any relevant data points you want to highlight. For example if I select the Diesel vehicle type I may want to know the average sales amount, total sales in dollars, or number of units sold. This is where we can build specific measures that will intelligently highlight selected data within your PowerBI visual.

Here is a sample of what we will be building today:

lets begin with starting with some data. In honor of your news feed being bombarded with Pokemon Go articles lets enter some data on Pokemon characters.

We will enter our data manually. For a full tutorial on manually entering in data visit here.

Click the Enter Data button on the Home ribbon and enter the following information into the displayed table.

Pokemon XP

Pikachu 1200

Weedle 650

Pidgey 800

Golbat 300

Rename the table to Characters. Once you are finished entering in the data it should look like the following:

Create Table of Characters

Click Load to continue.

Start to examine your data by building a table visual.

Table Visual

Next add a Bar chart.

Bar Chart

Note: I added the XP column twice. Once to the Value attribute and to the Color Saturation. This enhances the look of your visual by coloring the bars with a gradient. The largest bar will have the darkest color, and the smallest bar will have the lightest color.

Next, we will begin building some measures. The first measure will be a total of all the experience points (XP) for each character. Click the New Measure button on the Home ribbon and enter the following DAX expression:
```
Total XP = Sum(Characters[XP])
```
Now, add a Card visual and add the new measure we created Total XP.

Total XP Card Visual

This measure totals all the experience points for all the selected characters within the visual. Since all characters are now selected the total XP for all characters is 2,950.

The next, and final measure, will be the intelligent card. For this measure we want to display the characters name when we select them in the bar chart. Click the New Measure button on the Home ribbon and enter the following DAX expression:
```
Character(s) = IF( DISTINCTCOUNT(Characters[Pokemon]) = 1 , FIRSTNONBLANK('Characters'[Pokemon],'Characters'[Pokemon]) , DISTINCTCOUNT('Characters'[Pokemon]) & " Selected")
```
Update: As of Mid 2017 Microsoft introduced a new DAX expression called SELECTEDVALUE which greatly simplifies this equation. Below is an example of how you would change the DAX equation to use SELECTEDVALUE.
```
Selected = SELECTEDVALUE(  Characters[Pokemon],  DISTINCTCOUNT(  Characters[Pokemon]  )  &  " Selected" )
```
Explanation of this measure:

This measure first checks to see how many distinct items are in the column Pokemon of our dataset. If there is only one selected character then we will display the FIRSTNONBLANK character, which will be the name of our selected character. If there are more than one characters selected. The measure will count the number of characters selected and return a text string with the count and the word Selected. Thus, showing us how many items have been selected.

Add the measure titled Character(s) to a card visual.

Add Character Card Visual

We can now see that there are 4 characters selected. Clicking on Pikachu in the bar chart resolves with the character’s name being displayed and the XP of Pikachu being displayed in the Total XP card visual.

Selecting Pikachu

You can select multiple items by holding down Ctrl and clicking multiple items in the bar chart.

Well, that is it. I hope you enjoyed this Pokemon themed tutorial. Thanks for visiting.

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.
July 28, 2016
Measures – Month to Month Percent Change
I had an interesting comment come up in conversation about how to calculate a percent change within a time series data set. For this instance we have data of employee badges that have been scanned into a building by date. Thus, there is a list of Badge IDs and date fields. See Example of data below:

Employee ID and Dates

Looking at this data I may want to understand an which employees and when do they scan into a building over time. Breaking this down further I may want to review Q1 of 2014 to Q1 of 2015 to see if the employee’s attendance increased or decreased.

Here is the raw data we will be working with, Employee IDs Raw Data. Our first step is to Load this data into PowerBI. I have already generated the Advanced Editor query to load this file. You can use the following code to load the Employee ID data:
```
let
 Source = Csv.Document(File.Contents("C:\Users\Mike\Desktop\Employee IDs.csv"),[Delimiter=",", Columns=2, Encoding=1252, QuoteStyle=QuoteStyle.None]),
 #"Promoted Headers" = Table.PromoteHeaders(Source),
 #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Employee ID", Int64.Type}, {"Date", type date}}),
 #"Sorted Rows1" = Table.Sort(#"Changed Type",{{"Date", Order.Ascending}}),
 #"Calculated Start of Month" = Table.TransformColumns(#"Sorted Rows1",{{"Date", Date.StartOfMonth, type date}}),
 #"Grouped Rows" = Table.Group(#"Calculated Start of Month", {"Date"}, {{"Scans", each List.Sum([Employee ID]), type number}})
in
 #"Grouped Rows"
```
Note: I have highlighted Mike in red because this is custom to my computer, thus, when you’re using this code you will want to change the file location for your computer. For this example I extracted the Employee ID.csv file to my desktop. For more help on using the advanced editor reference this tutorial on how to open the advance editor and change the code, located here.

Next name the query Employee IDs, then Close & Apply on the Home ribbon to load the data.

Close and Apply

Next we will build a series of measures that will calculate our time ranges which we will use to calculate our Percent Change (% Change) from month to month.

Now build the following measures:

Total Scans, sums up the total numbers of badge scans.
```
Total Scans = SUM('Employee IDs'[Scans])
```
Prior Month Scans, calculates the sum of all scans from the prior month. Note we use the PreviousMonth() DAX formula.
```
Prior Month Scans = CALCULATE([Total Scans], PREVIOUSMONTH('Employee IDs'[Date]))
```
Finally we calculate the % change between the actual month, and the previous month with the % Change measure.
```
% Change = DIVIDE([Total Scans], [Prior Month Scans], blank())-1
```
Completing the new measures your Fields list should look like the following:

New Measures Created

Now we are ready to build some visuals. First we will build a table like the following to show you how the data is being calculated in our measures.

Table of Dates

When we first add the Date field to the chart we have a list of dates by Year, Quarter, Month, and Day. This is not what we want. Rather we would like to just see the actual date values. To change this click the down arrow next to the field labeled Date and then select from the drop down the Date field. This will change the date field to be viewed as an actual date and not a date hierarchy.

Change from Date Hierarchy

Now add the Total Scans, Prior Month Scans, and % Change measures. Your table should now look like the following:

Date Table

The column that has % Change does not look right, so highlight the measure called % Change and on the Modeling ribbon change the Format to Percentage.

Change Percentage Format

Finally now note what is happening in the table with the counts totaled next to each other.

Final Table

Now adding a Bar chart will yield the following. Add the proper fields to the visual. When your done your chart should look like the following:

Add Bar Chart

To add a bit of flair to the chart you can select the Properties button on the Visualizations pane. Open the Data Colors section change the minimum color to red, the maximum color to green and then type the numbers in the Min, Center and Max.

Changing Bar Chart Colors

Well, that is it, Thanks for stopping by. Make sure to share if you like what you see. Till next week.

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.
July 14, 2016
Loading Data From Folder

Let me setup a scenario for you. You get a data file from an automated system, it has the same number of columns but the data changes for every new file. Being the data savvy person that you are you’ve spent some time working in excel to make a template where you can copy your new data into and then automatically all your equations and graphs magically work. You pat your self on the back and happily send out your fantastic report to everyone you know. Then tomorrow when the data comes to you again you repeat the same process over again. Still enamored by your awesome report, you send it out again knowing you have saved your self so much time not having to do the analysis or creation of your reports over and over again. Now, fast forward 3 months. That stupid report shows up again, and now you have to lug all that data from file to file and begrudgingly you sent out your report. Thus, is the store of the analyst. You love data, but you hate it as well. Well in this tutorial I’ll show you how to remove some of the pain of that continual data loading process by loading new data from a folder.

My previous post (found here) talks about loading data from a folder. In this tutorial we will add some logic to this method that will look at a folder but only load the most recently added item from that folder.

Data for this tutorial is located this link Monthly Data Zip File. This data in the ZIP file is a monthly data sample from Feb 2016 to April of 2016.

Download the zip file mentioned above and extract the Monthly Data folder down to your desktop. Open up PowerBI Desktop and click on the Get Data button and select All on the left side. Click on the item labeled Folder and click Connect to continue.

Get Data from Folder

Select the newly unzipped Monthly Data folder that should be on your desktop. Click OK to continue. Upon opening that folder location you will be presented with the multiple files. Click Edit to edit the query.

Edit Query for Folder Load

Now you are in the Query Editor. This is where the fancy query editing will work to our advantage. We could load all the data into one large query. However, depending on the size of your data sets or how you want to report your data this may not always be desirable. Instead you may only want data from April, then May when the new data is sent next month.

Thus, our first step to start pairing down the data will be to first filter the files in sequential order. In this case because I have named the files with a Year-Month-Day format I can sort the files according to their names.

Note: When using PowerBI desktop it is a good practice to name the files beginning with a YYYY-MM-DD file name. This makes it really easy when sorting and ingesting information into PowerBI. I have used other columns of information such as Date Accessed or Date Created before but have gotten inconsistent results as these dates can change depending on when a file was moved or copied from one place to another.

Click the drop down next to Name and sort the files in Sort Descending.

Name in Sort Descending

This places the files with the most recent file at the top of the list.

File List in Descending Order

Next click on the Keep Rows button on the Home ribbon, select Keep Top Rows.

Keep Top Rows

Enter the number 1 when the popup appears. Click OK to continue.

Keep Top Rows Menu

Now you’ll notice you have only one file selected which is our latest file from April. Click the Load File button found in the Content column.

Load File Button

We have completed the activities in the Query Editor and can now load the data. Click Close & Apply found on the Home ribbon. All our April data has loaded. by making a simple table we can now see all the data that was just loaded.

Loaded Data from April

Now we will remove some data from our desktop folder labeled monthly data. Open the folder on the desktop labeled Monthly Data and delete the filed labeled 2016-04-01 April. You should now have a folder labeled Monthly Data with only two files in it, one for Feb and one for March.

Two Files Left

Return back to Power BI Desktop and click the Refresh button on the Home ribbon. Notice now how all our data has changed. We are now looking at the March data because it is the most recent file in our folder based on the file name.

March Data Load

To verify this we open the query editor (Click the Edit Queries on the Home ribbon). Click Refresh Preview on the Home ribbon and finally select the Applied Step called Kept First Rows. This will reveal the month of March as our data source.

Month of March Loaded

Now, every time you add a new file to our folder and refresh PowerBI the latest file (based on the naming convention we talked about earlier) will always be loaded.

Note: This method works great when your data source is coming from an automated system. The file format must always be the same for this to work reliability. If the file naming convention changes, or the number of columns or location of those columns changes then the query will most likely fail.

Good luck and thanks for following along.

June 16, 2016
Measures – Dynamic CAGR Calculation in DAX
This tutorial walks through calculating a dynamic Compound Annual Growth Rate (CAGR). By dynamic we mean as you select different items on a bar chart for example the CAGR calculation will update to reveal the CAGR calculation only for the selected data. See the example below:

Lets start off by getting some data. For this tutorial we will gather data from World Bank found here. To make this process less about acquiring data and more about calculating the CAGR. Below is the Query Editor code you can copy and paste directly into the Advance Editor.
```
let
 Source = Excel.Workbook(Web.Contents("https://powerbitips03.blob.core.windows.net/blobpowerbitips03/wp-content/uploads/2017/11/Worldbank-DataSet.xlsx"), null, true),
 EconomicData_Table = Source{[Item="EconomicData",Kind="Table"]}[Data],
 #"Changed Type" = Table.TransformColumnTypes(EconomicData_Table,{{"Country Name", type text}, {"Country Code", type text}, {"Indicator Name", type text}, {"Indicator Code", type text}, {"1960", type number}, {"1961", type number}, {"1962", type number}, {"1963", type number}, {"1964", type number}, {"1965", type number}, {"1966", type number}, {"1967", type number}, {"1968", type number}, {"1969", type number}, {"1970", type number}, {"1971", type number}, {"1972", type number}, {"1973", type number}, {"1974", type number}, {"1975", type number}, {"1976", type number}, {"1977", type number}, {"1978", type number}, {"1979", type number}, {"1980", type number}, {"1981", type number}, {"1982", type number}, {"1983", type number}, {"1984", type number}, {"1985", type number}, {"1986", type number}, {"1987", type number}, {"1988", type number}, {"1989", type number}, {"1990", type number}, {"1991", type number}, {"1992", type number}, {"1993", type number}, {"1994", type number}, {"1995", type number}, {"1996", type number}, {"1997", type number}, {"1998", type number}, {"1999", type number}, {"2000", type number}, {"2001", type number}, {"2002", type number}, {"2003", type number}, {"2004", type number}, {"2005", type number}, {"2006", type number}, {"2007", type number}, {"2008", type number}, {"2009", type number}, {"2010", type number}, {"2011", type number}, {"2012", type number}, {"2013", type number}, {"2014", type number}, {"2015", type number}, {"2016", type number}, {"2017", type any}}),
 #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Country Name", "2011", "2012", "2013", "2014", "2015", "2016"}),
 #"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each [2011] <> null and [2012] <> null and [2013] <> null and [2014] <> null and [2015] <> null and [2016] <> null),
 #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Filtered Rows", {"Country Name"}, "Attribute", "Value"),
 #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Country Name", "Country"}, {"Attribute", "Year"}, {"Value", "GDP"}})
in
 #"Renamed Columns"
```
Note: The tutorial on how to copy and paste the code into the Query Editor is located here.

Paste the code above into the advance editor. Click Done to load the query into the the Query Editor. Rename the Query to World GDP and then on the home ribbon click Close & Apply.

World GDP Query

Loading the query loads the following columns into the fields bar on the right hand side of the screen.

Fields Load from World GDP

Next we will build a number of measure that will calculate the required variables to be used in our CAGR calculation. For reference the CAGR calculation is as follows: (found from investopia.com)

CAGR Calculation

For each variable on the right of the equation we will create one measure ; one for Ending Value, Beginning Value and # of Years. On the Home ribbon click the button labeled New Measure. Enter the following equation for the beginning value:
```
Beginning Value = CALCULATE(SUM('World GDP'[GDP]),FILTER('World GDP','World GDP'[Year]=MIN('World GDP'[Year])))
```
This equation totals all the items in the table called World GDP in the column labeled GDP. This calculation will change based on the selections in the page view.

Add two more measures for Ending Value and # of years
```
Ending Value = CALCULATE(SUM('World GDP'[GDP]),FILTER('World GDP','World GDP'[Year]=MAX('World GDP'[Year])))
```
```
# of Years = (MAX('World GDP'[Year])-MIN('World GDP'[Year]))
```
Your fields list should now look like the following:

Fields List with Measures

Next add a Card visual for each new measure we added. A measure is illustrated by the little calculator image next to the measure. I have highlighted the Ending Value measure as a card for an example.

Ending Value Measure as Card Visual

Combining all the previous measures we will now calculate the CAGR value. Add one final measure and add the following equation to calculate CAGR:
```
CAGR = ([Ending Value]/[Beginning Value])^(1/[# of Years])-1
```
This calculation uses the prior three measures we created. Add the CAGR as a card visual to the page.

Card Visual for CAGR

Notice how the value of this measure is listed as a decimal, which isn’t very useful. To change this to a percentage click on the measure CAGR item in the Fields list. Then on the Modeling ribbon change the format from General to Percentage.

Format Change to Percentage

This changes the card visual to now be in a percentage format.

Percentage Format

Now you can add some fun visuals to the page and depending on what is selected the CAGR will change depending on the selected values.

ProTip: To calculate the CAGR you can alternatively compute the entire calculation into one large measure like so:
```
CAGR = ( [Ending Value] / [Beginning Value] )^(1/ [# of Years] )-1

is the same as below:

CAGR = (  CALCULATE(SUM('World GDP'[GDP]),FILTER('World GDP','World GDP'[Year]=MAX('World GDP'[Year])))  /  CALCULATE(SUM('World GDP'[GDP]),FILTER('World GDP','World GDP'[Year]=MIN('World GDP'[Year]))) ) ^ (1/  (MAX('World GDP'[Year])-MIN('World GDP'[Year]))  )-1
```
A final recommendation is to wrap the CAGR calculation in an IFERROR function to make sure if one year is selected the measure doesn’t fail. This returns a 0 if there is a calculation error of the equation. Documentation on IFERROR is found here.
```
CAGR = IFERROR( ([Ending Value]/[Beginning Value])^(1/[# of Years])-1 , 0)
```
To finish out the tutorial you can add the following visuals:

Stacked Bar Chart Visual – GDP by Year

Stacked Bar Chart Visual – GDP by Country

Note: you can sort the items in the stacked bar chart by selecting the ellipsis (the three dots in the upper right hand corner) and then selecting Sort By and clicking GDP.

Country Sorted by GDP

Finally select different items in the GDP by Year chart or the GDP by Country chart. To select more than one item in the bar charts you have hold shift and left mouse click the multiple items. Notice how all the measures change.

Years 2013 & 2014 CAGR

Thanks for following along.

This tutorial used the following materials:
- Power BI Desktop (I’m using the April 2016 version, 2.34.4372.322) download the latest version from Microsoft Here.
- Page from World Bank: http://data.worldbank.org/indicator/NY.GDP.MKTP.CD
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.
May 27, 2016
Measures – Calculating a Sum
Often there are times when you will want to display a totals. Using measures to calculate a total are extremely easy to use. The power of using a measure is when you are slicing and selecting different data points on a page. As you select different data points the sum will change to reflect the selected data. See sample of what we will be building today below.

Materials for this Tutorial are:
- Power BI Desktop (I’m using the April 2016 version, 2.34.4372.322) download the latest version from Microsoft Here.
- CSV file with data, download SampleData in CSV format.
To load the CSV file into Power BI Desktop you can follow along in this tutorial, Import CSV File into PowerBI Desktop.

Once you’ve loaded the CSV file into Power BI Desktop your fields items should resemble the following:

Fields List

Add the Table visual from the visualizations bar into the Page area. Drag the following items into the newly created table visualization, Category, Sales, and ID. Your table should look like the following:

Table of Data

Click the Triangle next to the ID column under the Values section in the Visualization bar. A menu will appear, select the top item labeled Don’t Summarize.

Do not Summarize Data for ID

This reveal all the unique items in our table of data. Now, we will create our measures for calculating totals. On the Home ribbon click the New Measure button. Enter in the following DAX expression.
```
Total Sales = SUM(SampleData[Sales])
```
Note: In the equation above everything before the equals sign is the name of the measure. All items after the equation sign is the DAX expression. In this case we are taking a SUM of all the items in the Table SampleData from the column labeled Sales.

This will total all the items in the sales column. Click on the Card visual and add the Total Sales measure to the card. Your new card should look like the following.

Total Sales Measure

Next we will add a bar chart to show how the data changes when the user selects various items on the page to filter down to different results. Add the Stacked Bar Chart to the page. In the Axis & Legend selectors add the Category column, and add the Sales column to the Value selector. This will yield the following bar chart.

Bar Chart

Now we can click on items in the bar chart to see how the table of data and the Total Sales changes for each selection. Clicking on the bar labeled Apples provides a total sales of 283, and clicking on the Oranges shows a total of 226.

Apples Bar Selected

Our measure is complete. Now we can select different visualizations and each time we do PowerBI is filtering the table of available data down to a smaller subset.

Pro Tip: When building different visuals and measures often it is helpful to have a table showing what data is being filtered when you interact with the different visuals. Sometimes the filters that you are applying by clicking on a visual interact in non-expected ways. The table helps you see these changes.

We have now completed a measure that is calculating a total of all the numeric values in one column.

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.
May 7, 2016
Measures – Calculating % Change
In our last post we built our first measure to make calculated buckets for our data, found here. For this tutorial we will explore the power making measures using Data Analysis Expressions (DAX).

When starting out in Power BI one area where I really struggled was how to created % change calculations. This seems like a simple ask and it is if you know DAX.

Alright lets go find some data. We are going to go grab data from Wikipedia again. I know, the data isn’t to reliable but it is fun to play with something that resembles real data. Below is the source of data:
```
https://en.wikipedia.org/wiki/Automotive_industry
```
To acquire the data from Wikipedia refer to this tutorial on the process. Use the Get Data button, click on Other on the left, select the first item, Web. Enter the webpage provided above in the URL box. Click OK to load the data from the webpage. For our analysis we will be completing a year over year percent change. Thus, select the table labeled By Year[edit]. Data should look like the following:

Global Auto Production Wikipedia

This is the total number of automotive vehicles made each year globally from 1997 to 2014. Click Edit to edit the data before it loads into the data model. While in the Query Editor remove the two columns labeled % Change and Source. Change the Name to be Global Production. Your data will look like the following:

Global Production Data

Click Close & Apply on the Home ribbon to load the data into the Data Model.

Add a quick visual to see the global production. Click the Area Chart icon, and add the following fields to the visual, Axis = Year, Values = Production. Your visual should look something like this:

Area Chart of Global Production

Next we will add a table to see all the individual values for each year. Click the Table visual to add a blank table to the page. Add Both Year and Production to the Values field of the visual. Notice how we have a total for both the year and production volumes. Click the triangle next to Year and change the drop down to Don’t summarize.

Change to Don’t Summarize

This will remove the totaled amount in the year column and will now show each year with the total of Global Production for each year. Your table visual should now look like the following:

Table of Global Production

Now that we have the set up lets calculate some measures with DAX. Click on the button called New Measure on the Home ribbon. The formula bar will appear. We will first calculate the total production for 2014. We will build on this equation to create the percent change. Use the following equation to calculate the sum of all the items in the production column that have a year value of 2014.
```
Total 2014 = CALCULATE(sum('Global Production'[Production]),FILTER('Global Production','Global Production'[Year] = 2014))
```
Note: I know there is only one data point in our data but go alone with me according to the principle. In larger data sets you’ll most likely have multiple numbers for each year, thus you’ll have to make a total for a period time, a year, the month, the week, etc..

This yields a measure that is calculating only the total global production in 2014. Add a Card visual and add our new measure “Total 2014” to the Fields. This shows the visual as follows, we have 90 million vehicles produced in 2014.

2014 Production

Repeat the process above instead use 2013 in the Measure as follows:
```
Total 2013 = CALCULATE(sum('Global Production'[Production]),FILTER('Global Production','Global Production'[Year] = 2013))
```
This creates another measure for all the production in 2013. Below is the Card for the 2013 Production total.

2013 Production

And for my final trick of the evening I’ll calculate the percent change between 2014 and 2013. To to this we will copy the portions of the two previously created measure to create the percent change calculation which follows the formula [(New Value) / (Old Value)]- 1.
```
% Change = CALCULATE(sum('Global Production'[Production]),FILTER('Global Production','Global Production'[Year] = 2014)) / CALCULATE(sum('Global Production'[Production]),FILTER('Global Production','Global Production'[Year] = 2013)) - 1
```
This makes for a long equation but now we have calculated % change between 2013 and 2014.

Percent Change

Wait you say. That seems really small, 0.03 % change is next to nothing. Well, I applaud you for catching that. This number is formatted as a decimal number and not a percentage, even though we labeled it as % change. Click the measure labeled % Change and then Click on the Modeling ribbon. Change the formatting from General to Percentage with one decimal. Notice we now have a percentage.

Change Format to Percentage

Thanks for working along with me. Stay tuned for more on percent change. Next we will work on calculating the percent change dynamically instead of hard coding the year values into the measures.

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.

Please share if you liked this tutorial.
May 2, 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
Manually Enter Data
There are often times when you need a small data set in order to make a visual behave exactly how you want it to. This may mean you need a small table to represent a range of numbers or text values.

Here are the Resources for this tutorial:
- Power BI Desktop (I’m using the March 2016 version, 2.33.4337.281) download the latest version from Microsoft Here.
To enter your own data Click the Enter Data button on the Home ribbon.

Enter Data Button

Next you are prompted with the Create Table window. In this window you are given the layout of a unfilled table. To begin entering data you can click in the first cell in Column one and start entering data. By pressing enter a new cell will populate below. You can Rename the column by double clicking the column name. To add a second column you Click on the * symbol next to your existing column. Finally to edit the table name you can type in the desired table name in the Name input box in the bottom left hand portion of the window.

Create Table Window

Finally, you can either to choose to Load the data as is or Edit the data to make additional changes (this can be useful to edit the data types of each column or to populate equations in subsequent columns). For the sake of this tutorial we will simply load the data. Click Load to load the data into the data model.

Now drag over the columns into the page view to begin generating visuals. By default PowerBI makes a table of data to show you the values you just entered.

Visual of Sales Table

Select the table visual (you know it is highlighted when it has the trim boarder as shown above) and Click the Doughnut Visual. This transforms the data into a doughnut, and who doesn’t like a nice data doughnut? Click anywhere in the page to de-select the new doughnut visual. Add a second table by dragging over he Region and Sales columns. We can now see the pretty graphic and the numbers supporting that visual.

Visuals Made with our Custom Data

I bet you didn’t notice that something changed here. Look closely at the data we see now vs. what we entered earlier. Go ahead, scroll up, I’ll wait… Did you catch it?

We now have 5 rows of data but we entered 6 before. That is because the Sales column is a number column and can be aggregated. Look in the fields column and you see there is a little sum symbol in front of the Sales column. This means that this column has a default summarization associated. To see what is the default summarization highlight Sales by clicking on the column name in the grey area. Then Click the ribbon titled Modeling, and there it is in the properties section the Default Summarization is Sum. Every time you use the Sales column it will be summarized in the tables and visuals views. Our visual table shows Brazil with a total sales of 600, because we had two Regions labeled as Brazil 500 and 100.

Now you can click on any of the data points in the doughnut. Notice the table automatically filters down to only show the areas you selected.

Data Filtered to only Brazil

ProTip: you can select multiple selections by holding down CTRL and selecting multiple items in the visual. You can only do this inside of one visual. As soon as you click another visual all filtering will disappear.

Again, I hope you enjoyed this quick tutorial. If you liked it make sure you share it below.
April 13, 2016

Pokemon	XP
Pikachu	1200
Weedle	650
Pidgey	800
Golbat	300