PowerBI.tips

Month: May 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
Query Editor – Editing M Code

In this tutorial we’ll learn how to copy and paste queries to and from the Query Editor. When your working in Power BI Desktop often you will need to share and model the data before it can be applied to the visual. In my experience you’ll need to add a calculated column or break out a date such as 1/5/2016 into the the Year 2016 or Month 01, components to properly display a visual.

We will start off with from a prior example where we build a shaded region map. The tutorial to create this Power BI Desktop file is located here.

If you want to cheat and download the final PBIX file you can download and open the zipped file here: Regional Filled Map Example

This file was made in Power BI Desktop April 2016 version, 2.34.4372.322, download the latest version from Microsoft Here.

Open the zip file that you downloaded and extract the file inside labeled Regional Filled Map Example. Open the file. Once you’ve opened the file on page 1 of the you see a map of the united states that looks similar to the following.

Opened File with Map

Now we well enter the query editor. Click on the Edit Queries on the Home ribbon. You opened the Query Editor. In this window we shape and model the data so we can properly visualize it on the pages. Couple of things to notice here. Every time you press a button on the ribbon, the query editor generates an Applied Step. Each step writes a line of M code which transforms the data as it is loaded into the computer’s memory. In this case we have (7) seven steps starting at Source and ending with Changed Type1.

Query Editor Revealing Applied Steps

We want to expose the code that is begin generated at every step behind the scenes. Click on the View ribbon and then click on the button called Advanced Editor.

Query Editor – Advanced Editor

Opening this window reveals the M language code that is generating each Applied Step we saw earlier.

Note: some of the steps we saw earlier such as Filtered Rows had a space in it. In the query editor any applied step had a space in the name gets the added #”” around the applied step name. Thus, in the query editor Filter Rows would be #”Filtered Rows”. The hashtag and the quotations define the complete variable. If you changed the name of the applied step to FilteredRows, with no space. In the Advanced Editor you’d only see the step labeled as FilterRows, no hastag or quotations needed.

Now that the M language is revealed you can made modifications to the code. In cases where you want to make a function you would do so in the Advanced Editor. For our example today select all the code and copy it to the clipboard using the keyboard shortcut CTRL+C. Click Done to close the window.

Now lets copy that code into a brand new query. Click the Home ribbon, then click New Source, scroll all the way to the bottom of the list and select Blank Query. Click Connect to start a blank query.

Get Data – Blank Query

A new Query will now open up. Click the View ribbon, then click Advanced Editor. A blank editor window will open.

Blank Query

Paste the code we copied earlier into this window. Now the new Query1 should look like the following:

Paste Code in to Advance Editor

Click Done and the new query will now load. It is that simple, now we have two identical queries.

May 19, 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