Measures – Dynamic Percent Change – Using DAX

Feature Image - % Change

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.

let
 Source = Web.Page(Web.Contents("https://en.wikipedia.org/wiki/Automotive_industry")),
 Data0 = Source{0}[Data],
 #"Removed Columns" = Table.RemoveColumns(Data0,{"Change", "Source"}),
 #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Production", Int64.Type}, {"Year", Int64.Type}})
in
 #"Changed Type"

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:

Production
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
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
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
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.

% Change = 
DIVIDE(
 CALCULATE(
 SUM('Auto Production'[Production]), 
 FILTER('Auto Production','Auto Production'[Year]=MAX('Auto Production'[Year])
 )
 ), 
 CALCULATE(
 SUM('Auto Production'[Production]),
 FILTER('Auto Production','Auto Production'[Year]=MIN('Auto Production'[Year])))
,0) 
- 1

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
Add Card

Change the % Change measure format from General to Percentage, do this on the Modeling ribbon under Formatting.

Change Measure Formatting
Change Measure Formatting

Next add a slicer for Year. 

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
Selecting Two Years

If we select a year between 2013 and 2007 the measure will not change.

Multiple Years Selected
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
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.

It's only fair to share...
Share on Facebook0Share on Google+0Tweet about this on TwitterShare on LinkedIn0Pin on Pinterest0Share on Tumblr0

6 Comments

  1. Thanks, Great Tutorial, but maybe you know how to calculate for example same value month to month to compare monthly data vs previous year … January February March … (2015vs2016) and filtered of course, it would be great to know.

    • Great question. I have already anticipated this question and have developed a separate tutorial just for this instance. Check out this tutorial on year over year percent changes.

  2. The visual at the top with the years listed out horizontally across the page. How did you create that? I’ve been playing around with the visuals for a couple days now. Thanks!

    • This is a setting that has recently been moved. The visual that makes up the years is a slicer. If you use a slicer with numbers it defaults to a slicer bar. You’ll need to change this to a list. To do this you’ll need to hover your mouse just under the ellipsis in the white space. Two little icons will appear. The first is an eraser, the second is a down arrow. Click on the down arrow and change the slicer to a list. Once the slicer is a list then click on the Paint Roller in the Visualizations Panel. Then under the general section change the orientation from Vertical to Horizontal. Now you will have a list of items that can be filtered on just like in my Year slicer.

  3. for some reason the loading of the data from the source gave errors (like previous …) this time I could retrieve the appropriate data and I just copy and paste the

    query

    in case someone else experience similar problems …

    let
    Source = Web.Page(Web.Contents(“https://en.wikipedia.org/wiki/Automotive_industry”)),
    Data0 = Source{0}[Data],
    #”Changed Type” = Table.TransformColumnTypes(Data0,{{“Year”, Int64.Type}, {“Production”, type number}, {“Change”, Percentage.Type}, {“Source”, type text}}),
    #”Removed Columns” = Table.RemoveColumns(#”Changed Type”,{“Change”, “Source”}),
    #”Changed Type1″ = Table.TransformColumnTypes(#”Removed Columns”,{{“Production”, Int64.Type}})
    in
    #”Changed Type1″

    hopefully I can continue the rest of this section as meant to be 😀

    • Henrike, Excellent find. This was a more recent change when loading data occured. When I originally wrote this post the headers of the table did not automatically populate. I have changed the M code to reflect the correction you found! I appreciate the help keeping me honest. Have a blessed day.

Leave a Reply

Your email address will not be published.


*


Comment moderation is enabled. Your comment may take some time to appear.