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. ClickAdvancedEditor 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.
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.
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.
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.
Next, add a Stacked Column Chart with the following columns selected.
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.
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.
Click on the Title change it something meaningful, Center the text and increase the font size.
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.
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.
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 Calcswith 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.
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!
Recently at work I’ve been working with a number of large data warehouses with time series data. Often when working on such data you need to incorporate a data calendar to compute date ranges. So, for this tutorial we will build a custom date table directly inside PowerBI.
Start by opening up power BI and clicking Get Data on the home ribbon, then select Blank Query. Like always make sure you start by re-naming the query into something meaningful. Change the name of the Query to Date List. Next enter the following equation into the formula bar:
Note: For more information on the M language you can visit here. Also, here is the link to the List.Dates function found here.
Once we enter the formula into the formula bar the list of dates will appear below.
The quick explanation about the List.Dates function is below. I’ve simplified the variables below:
List.Dates( Start Date , Number of intervals , Type of interval )
While this is interesting it does not help us make a report that updates the date range dynamically. The real world use case for this would be you have a report with data that is being generated daily, say for example a website. Maybe you want a custom date range that automatically changes every day you log into PowerBI. For example if today is 08-20-2016, I want the first date to be today and then list the dates that previous 10 days.
Note: In this equation we have changed the duration to -1. This is important to note because now our date table returns older dates. In our previous equation we used a positive 1 and we return future dates.
In this new equation we have defined the Start Date to the following statement : DateTime.Date( DateTime.FixedLocalNow() ) This is tricky because if you only use DateTime.FixedLocalNow() the statement will error out. The error occurs because the DateTime.FixedLoaclNow() is a date and time. The List.Dates function is expecting a Date only value. Hence why we use the DateTime.Date() function to remove the time stamp and only return today’s date.
It is most likely your date ranges will be different than the ones in the example because the DateTime.FixedLocalNow() function will be pulling in your computer’s current date.
Next modify the equation to now pull the last 90 days (highlighted in red below)
The list of dates is just that a list. We really can’t do to many other enhancements to our data with only a list of dates. Now transform the list into a table. Click on the Transform ribbon and select To Table. Notice now that we have a new column and a new applied step.
I colored the first null in the equation. This is actually a parameter that you can use to name the new column we just made. Tricky, Tricky, PowerBI. Modify the equation to the following:
Our table is updated and now has the name Date. Nice work!
Now lets make our date list useful. Click on the ribbon labeled Add Column and then the button labeled Add Custom Column. Add the following equation to the new column and name it Week #, then click OK, to continue.
This equation defines the start of the week highlighted in RED. Since today is Tuesday 8/30/16, then the days 8/30 (Tues), 8/29 (Mon), 8/27 (Sunday) are considered week 0 or the current week. All dates prior will start with weekly increment.
Now we can add some logic to define week variables. Click on the Add Column ribbon and select the Conditional Columnbutton. Using the drop downs in Column Name, Operator, Value and Output enter the following:
Click OK to proceed. We have now added an additional column with a text description of the week.
Following the add column steps mentioned above we will now add more week descriptions. Add the following conditional column for Last Week:
From here you can make custom columns for how you want to describe your data. In this example we will build last 2 weeks, 3 weeks and last 4 weeks. See the add conditional column logic for each of those respective weeks.
Conditional Column Logic for last 2 weeks:
Note: When we added this conditional column we label week 0 as last 2 weeks. See image below as an example:
To fix this we modify the code that generated this column. The code initially states the following:
= Table.AddColumn(#"Added Conditional Column1", "Last 2 Weeks", each if [Week Number] < 3 then "Last 2 Weeks" else null )
We modify this code to the following: (changes highlighted in bold)
= Table.AddColumn(#"Added Conditional Column1", "Last 2 Weeks", each if [Week Number] < 3 and [Week Number] > 0 then "Last 2 Weeks" else null )
This now removes the first three days from our Last 2 Weeks column reflecting a more accurate picture of our time ranges.
Next we will add the Last 3 Weeks column and the Last 4 weeks column. Each time we will modify the add column code to remove the first three dates of the current week.
Last 3 Weeks auto generated code:
= Table.AddColumn(#"Added Conditional Column2", "Last 3 Weeks", each if [Week Number] < 4 then "Last 3 Weeks" else null )
We modify to the following to achieve the correct Last 3 Weeks data range: (changes highlighted in bold)
= Table.AddColumn(#"Added Conditional Column2", "Last 3 Weeks", each if [Week Number] < 4 and [Week Number] > 0 then "Last 3 Weeks" else null )
Add the Last 4 Weeks column:
Last 4 Weeks auto generated code:
= Table.AddColumn(#"Added Conditional Column3", "Last 4 Weeks", each if [Week Number] < 5 then "Last 4 Weeks" else null )
Modify the code the following to correct the column: (changes highlighted in bold)
= Table.AddColumn(#"Added Conditional Column3", "Last 4 Weeks", each if [Week Number] < 5 and [Week Number] > 0 then "Last 4 Weeks" else null )
Nice job so far. We are almost to the end now. After all those additional columns you should have something that looks similar to the following:
Next we will pivot all the data down to one column. This will enable us to select a time period and automatically have our date table update to the specific range.
First, shift select the following columns, Current Week, Last Week, Last 2 Weeks, Last 3 Weeks, and Last 4 Weeks. Then on the Transform ribbon click the Unpivot Columns button.
Next delete the Attribute column using a right click on the Attribute column and selecting Remove Columns.
Rename the Value column to Selector by right clicking on the Value column.
Modify each column to have the correct Data Type on the Home ribbon.
Date column data type should be Date
Week Number column data type should be Whole Number
Selector column data type should be Text
Note: It is important to always check your data types for each column before you leave the Query Editor. If you don’t you’ll find that the visuals that your trying to build later on on the page view will not work as expected.
Next, click the Home ribbon and select Close & Apply. You can now build the following visuals:
A slicer for the Selector column:
Table visual for the Date column:
Note: When you use the Date Column as the data source for the Table Visual the data will automatically be added as a Date Hierachy. This does not work well with our data so you will need to change the date from a Date Hierarchy to a standard Date. To do this click the little triangle next to the Date in the Values box. Then select Date.
Now you can finally play around with your data and by selecting different items in the Selector slicer you can filter down to different date ranges. Below I selected the Last Week item, which filters down my dates to only the 7 days from last week.
Nice job making a custom date table in PowerBI. The nice part about this table is that it will always refresh with the latest dates whenever the queries are refreshed for this PowerBI file.
Bonus: For those of you who want to cheat and just have the M code to generate this custom date table it can be used from here:
let
Source = List.Dates(DateTime.Date(DateTime.FixedLocalNow()), 90, #duration(-1,0,0,0)),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error),
#"Added Custom1" = Table.AddColumn(#"Converted to Table", "Week Number", each Number.RoundDown( Number.From(Date.AddDays( List.Max( Table.Column(#"Converted to Table", "Date" ) ) , -1 * Number.From( List.Max( Table.Column(#"Converted to Table", "Date" ) ) - Date.StartOfWeek( List.Max( Table.Column( #"Converted to Table", "Date" ) ) , Day.Saturday ) ) ) -[Date] ) / 7 + 1 , 0)),
#"Added Conditional Column" = Table.AddColumn(#"Added Custom1", "Current Week ", each if [Week Number] = 0 then "Current Week" else null ),
#"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "Last Week", each if [Week Number] = 1 then "Last Week" else null ),
#"Added Conditional Column2" = Table.AddColumn(#"Added Conditional Column1", "Last 2 Weeks", each if [Week Number] < 3 and [Week Number] > 0 then "Last 2 Weeks" else null ),
#"Added Conditional Column3" = Table.AddColumn(#"Added Conditional Column2", "Last 3 Weeks", each if [Week Number] < 4 and [Week Number] >0 then "Last 3 Weeks" else null ),
#"Added Conditional Column4" = Table.AddColumn(#"Added Conditional Column3", "Last 4 Weeks", each if [Week Number] < 5 and [Week Number] > 0 then "Last 4 Weeks" else null ),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Added Conditional Column4", {"Date", "Week Number"}, "Attribute", "Value"),
#"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Value", "Selector"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}, {"Week Number", Int64.Type}, {"Selector", type text}})
in
#"Changed Type"
Often you will need to create some custom calendars within your PowerBI reports. Ruth Pozuelo from Curbal does a great video tutorial on using Calendar() and CalendarAuto(). I have use the Calendar() DAX function many times and find it very helpful. The following videos are built directly within DAX. This approach is one of many different methods that can be used to generate a list of dates. In a previous tutorial I talked about how to build a date table within the Query Editor (build date table in the Query Editor).
One method that Ruth talks about is the ability to use the CalendarAuto(). I have not used this expression in any previous reports, but seeing how simple it is to implement this will definitely have to be added to the toolbox.
Curbal has been generating a lot of great content. To learn about for more information you can visit the website found here, or visit the YouTube Channel.
For more great videos about Power BI click the image below:
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:
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:
Click Load to continue.
Start to examine your data by building a table visual.
Next add a 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.
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:
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.
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 FIRSTNONBLANKcharacter, 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.
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.
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.
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:
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.
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.
Completing the new measures your Fields list should look like the following:
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.
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.
Now add the Total Scans, Prior Month Scans, and % Change measures. Your table should now look like the following:
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.
Finally now note what is happening in the table with the counts totaled next to each other.
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:
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.
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.
This tutorial will produce a measure that will dynamically calculate a percent change every time an item is selected in a visual. The previous tutorial can be found here. In the previous tutorial we calculated the percent change between two time periods, 2014 and 2013. In practice it is not always desirable to force your measure to only look at two time periods. Rather it would be nice that your measure calculations change with changes in your selections on visuals. Thus, for this tutorial we will add some dynamic intelligence to the measures. Below is an example of what we will be building:
First here is the data we will be using. This data is the same data source as used in the previous % change tutorial. To make things easy I’ll give you the M code used to generate this query. Name this query Auto Production.
Note: the code shown above should be added as a blank query into the query editor. Add the code using the Advanced Editor. Another tutorial showing you how to add advanced editor code is here.
Once you’ve loaded the query called Auto Production. The Field list should look like the following:
Next add a Table with Production and Year. this will allow us to see the data we are working with. When you initially make the table the Year and Production columns are automatically summed, thus why there is one number under year and production.
Rather we want to see every year and the production values for each of those years. To change this view click on the triangle in the Values section of the Visualizations pane. This will reveal a list, in this list it shows that our numbers are aggregated by Sum change this to Don’t Summarize.
Now we have a nice list of yearly production levels with a total production at the bottom of our table.
Next we will build our measure using DAX to calculate the percent changes by year. Our Calculation for % change is the following:
% Change = ( New Value / Old Value ) - 1
Below is the DAX statement we use as our measure. Copy the below statement into a new measure.
I color coded the DAX expression between the two equations to show which parts correlated. Note we are using the DIVIDE function for division. This is important because if we run into a case where we have a denominator = 0 then an error is returned. Using DIVIDE allows us to return a zero instead of an error.
Next add our newly created measure as a Card.
Change the % Change measure format from General to Percentage, do this on the Modeling ribbon under Formatting.
Next add a slicer for Year.
Now you can select different year and the % change will automatically change based on our selection. The % change will always select the smallest year’s production and the largest year’s production to calculate the % Change. By Selecting the Year 2013 and 2007, the percent change is 19.15%. The smallest year is 2007 and the largest is 2013.
If we select a year between 2013 and 2007 the measure will not change.
The measure will only change when the starting and ending years are changed. By selecting the year 2014, the measure finally changes.
Pretty cool wouldn’t you say? Thanks for taking the time to walk through another tutorial with me.
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.
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.
Loading the query loads the following columns into the fields bar on the right hand side of the screen.
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)
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:
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.
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.
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.
This changes the card visual to now be in a 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:
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.
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.
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.
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.
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.
Once you’ve loaded the CSV file into Power BI Desktop your fields items should resemble the following:
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:
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.
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.
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.
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.
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.
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:
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:
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:
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.
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:
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.
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.
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.
This makes for a long equation but now we have calculated % change between 2013 and 2014.
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.
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.