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:
Previously we’ve done a tutorial on loading multiple text files within one query. This is nice, however we will also need to import multiple Excel files. First, to understand the procedure of querying multiple excel files you have to understand the basics between the CSV (comma separated values) file and an excel (.xls or .xlsx) files. In a CSV file you have only one data set. The beginning of the file starts with values and separates each file with a “,” a carriage return starts a new row of data. This is an easy and efficient way to store millions of rows of data. By contrast the excel file is way more complicated. Excel files can have multiple sheets of tables of data. Think of this as a stack of CSV type files. For example if you have an excel workbook with three sheets of data, Sheet 1, Sheet 2, Sheet 3. You can think of those three sheets as grid of data, similar to the CSV file. The multiple sheet aspects of an excel file makes the data ingestion into PowerBI a little bit more complicated. To add to the complication, when you loading data from either multiple sheets, or selecting a specific out of many sheets of data. For illustration purposes imagine working with two excel files with three sheets each, 2 x 3 = 6, a total of 6 sheets of data, or what I will call “pages” of data. This is why it is more complex to load excel files than CSV files.
Note: If you want to learn how to load multiple CSV files visit this tutorial.
Not only do you have to figure out what data you want to ingest on the page you must all tell PowerBI which sheets do you want to look at, and from which excel file. If that was to many words think of loading the following data sample:
Workbook 1 – Year 2000 Olympic Medals
Sheet 1
Olympic Medals Table
Rank
Country
Gold
Silver
Bronze
Total
Sheet 2
Sheet 3
WorkBook 2 – Year 2004 Olympic Medals
Sheet 1
Olympic Medals Table
Rank
Country
Gold
Silver
Bronze
Total
Sheet 2
Sheet 3
The data structure for both workbook 1 and 2 are similar but the names of the files are different and there can be multiple pages.
To resolve this we will have to write a M language function that will load each file as a function. This will be done in later in the tutorial.
Here is the data source information for Olympic medals won by each country from 2000 to 2012, download here. Inside the Medal Count zip file are four xlsx files, extract them to your desktop. Move the files into a folder on your desktop labeled Medals.
Medals Folder
Now, open up PowerBI, We will begin shaping our data to load all the excel files. On the Home ribbon click on the Get Data button. Select Folder on the right side and click Connect.
Get Folder Data
Next select the folder path that you want acquire the files from, Click OK to continue.
Load Folder Screen
Next we are presented with the loaded files within our selected folder. Click Edit at the bottom of the screen to proceed. The Query Editor window will now open. Select the first two columns labeled Content, and Name. With those two columns selected right click on the header and select Remove Other Columns. This will remove all the useless data associated with the files.
Remove Other Columns
Click the Add Column ribbon and press the Add Custom Column on the left side of the ribbon.
Add Custom Column
Name the new column ExcelFileLoad and enter the following equation.
Excel.Workbook Equation
Note: Once you type “Excel.Workbook(” you can click on the column labeled Content on the right side of the screen to have the name automatically added. This is useful when you have many many columns to choose from or if there naming of those columns becomes complex. This way you won’t type in the column name incorrectly.
Click OK to proceed. Notice we now have a new column called ExcelFileLoad. Next click the Expand button (the one with the arrows) located at the right of our newly added column. Click OK to proceed.
Expand Column Button
Now we have a new column labeled ExcelFileLoad.Data, which is the data contained in our excel files. Now click in the Grey Area next to the word labeled Table. This will open up the file and reveal the information present in the file. Notice that we can see the headers and the data in our file. Row 1 contains the headers of each column. Rows after row 1 contains the medal data.
View Data of File
Next select the columns labeled Name and ExcelFileLoad.Data and right click on the column header, then select Remove Other Columns
Remove Other Columns Again
On the Add Column ribbon click Add CustomColumn again. Name the column PromoteHeaders and enter the following formula. Click OK to proceed.
Promote Headers Step
Clicking again on the grey area in our newly created column reveals our tables with promoted headers.
View of Data with Promoted Headers
Next click the Expand Button, un-check the Use original column name as prefix and click the OK button to proceed.
Expand Data
Remove the following columns, ExcelFileLoad.Data, Rank, and Total, bu right selecting the columns and right clicking on the header and selecting Remove Columns. Now we want to parse out the year name from the Name column. To do this click on Name Column. Then click the Transform ribbon and click the Extract button, then select First Characters from the drop down menu.
Extract First Characters
In the Extract First Characters menu enter the number 4 and click OK to proceed.
Extract First 4 Characters
Change the following columns to whole numbers: Name, Gold, Silver, Bronze. Do this on the Transform ribbon in the Data Type drop down.
Change Data Types
We are now ready to load all the data. Rename the Query to Medals, click the Home ribbon and select Close & Apply.
Name Query
And there you have it. We have successfully loaded four excel files into one query.
Bonus: for added flare add the following measure.
Total Medal Count = sum(Medals[Gold]) + sum(Medals[Silver]) + sum(Medals[Bronze])
This week I encountered an issue when working with multiple queries in my data model. Here is the source files in case you want to follow along.
Here’s what happened. I had a PBIX file that had four queries in it, one file for the summer the Olympic metal count for the following years, 2000, 2004, 2008, and 2012.
Olympic Metal Count
After a bit of working I figured that my desktop screen was going to get to cluttered if I continued to collect Olympic metal data. Thus, I moved my excel files which were my source data into a folder called Olympic Medals.
File Move
By doing this I broke all the links for all four files. This was discovered when I tried to refresh my queries and noticed that all the queries failed. Power BI gave me a nice little message notifying me that there was a data source error.
DataSource.Error: Could not fine the file:
Missing File Error
To fix this I had to open the query editor and change each file’s location to the new folder that I just made. Seeing that this is not an efficient use of my time, I decided to spend more time to figure out a way to make a variable that would be my file location for all my queries.
Lets begin by making a new blank query by clicking on the bottom half of the New Source button on the Home ribbon. Then click the item labeled Blank Query.
Start Blank Query
With the new query open type in the file location where you will obtain all your working files. For me my file location was on my desktop, thus the file location is listed below. Rename the new query to Folder.
Folder Query
Note: Since we are working on building a file structure for Power BI to load the excel files you will want to be extra careful to add a “\” back slash at the end of the file location.
Next on the query for Medals 2000, we click the Source under the applied steps window on the right. This will expose the code in the formula bar at the top of the window.
Select the Source Applied Step
Note: If you don’t see the formula bar as I have illustrated in the image above, you can turn this feature on by click the View ribbon and checking the box next to the words Formula Bar. This will expose the formula bar so you can edit the source step.
This is where the magic happens. We can now insert our new blank query into this step. Our current file contents looks like the following:
Not only does this shorten our equation, it now uses the folder location we identified earlier and then we can pick up the file name 2000 Medals.xlsx. This makes is very easy to add additional queries with the same steps. Also, if you move your files to a new folder location, you only have to change the Folder query to reflect the new file location. To test this make a new folder on your desktop called New Folder. Move all the Olympic medal files to the new folder. Now in Power BI Desktop press the Refresh on the Home ribbon. This should result in the Data.Source.Error that we saw earlier. To fix this click the Edit Queries on the Home ribbon, select the Folder query and change the file directory to the new folder that you made on your desktop. It should look similar to the following:
New Folder Image
Once you’ve modified the Folder query, click Close & Apply on the Home ribbon and all your queries will now reload. Success!!
New Queries Loaded
Hope this tutorial helps and solves some of the problems when moving data files and storing information for Power BI desktop. Please Share if you like the tutorials. Thanks.
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:
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.
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.
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.
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.
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.
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:
Auto Production
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.
Table of Data
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.
Change to Don’t Summarize
Now we have a nice list of yearly production levels with a total production at the bottom of our table.
Table of Production Values by Year
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.
Add Card
Change the % Change measure format from General to Percentage, do this on the Modeling ribbon under Formatting.
Change Measure Formatting
Next add a slicer for Year.
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.
Selecting Two Years
If we select a year between 2013 and 2007 the measure will not change.
Multiple Years Selected
The measure will only change when the starting and ending years are changed. By selecting the year 2014, the measure finally changes.
Selecting Additional Year
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.
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 YearStacked 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.
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 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.
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:
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.
