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.

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

  4. Hi,
    Excellent and so helpful your tutorial. But I want to walk more behind and I ask you if there is a way to do the same but adding more filters like location or type of product.

    Something like “I’d like to compare US shirts sales in 2009 march vs France pants sales in 2010 june” I t could be awesome if it will be possible.

    Thanks again!

    • You have a great question. What your asking is for additional slicers for additional comparisons. In order to do this you would simply need to create a visual with additional columns. In this example we were only using two columns production and the year. There is now quick measures which you can use to help you quickly calculate a percent change. Read more about it here (https://powerbi.microsoft.com/en-us/blog/quick-measures-preview/). This will help you build a measure that will calculate percent change as well as percent changes YoY and can include additional columns like clothing type or country. Check out the quick measure Year over year change, I think that will be what you want.

      • Thanks but YoY change is not my hard. I’ll appreciate if you could do your example (production and year) again with at least one more variable like location (country) just for understanding the proccess. Im new in Power BI and its hard to me learning without examples, even english is not my native.

        I want do the same you did the dynamic percent change with 3 or more variables. Thanks a lot!

  5. Hi,

    I have a question. How can I add independent slicers for each both start and end year? I want to select at one slicer the start date and at the next one the finish date and the card still shows the change. Thanks for reading me.

    • In order to do this you can use the date slicer. in the date slicer you would be able to pick a start date and an end date. Then make two measures that look at the date column, first measure would have a MAX(date), and the other would have a MIN(date). Then when your calculating other measures you would use the min and max dates as filter context.

      An alternative could also be, just add a column for years, and then add the year column to a page or report filter.

  6. Great Post!…I’m trying to do this for week over week change for facebook total engagements. My sheet has posts from different days with their engagements but also has a column for the week (i.e. 7/31-8/16 would be a week name). I would want to roll those days up to see percent change. I modified your formula a bit but I think it went wrong somewhere. Am I overlooking something simple?

    FYI Date= “Date” the post was created

    % Change =
    DIVIDE(
    CALCULATE(
    SUM(‘All Sheets'[Total Engagements]),
    FILTER(‘All Sheets’,’All Sheets'[Date]=MAX(‘All Sheets'[Date])
    )
    ),
    CALCULATE(
    SUM(‘All Sheets'[Total Engagements]),
    FILTER(‘All Sheets’,’All Sheets'[Date]=MIN(‘All Sheets'[Date])))
    ,0)

    • Yes it should work, but remember the % change calculation will take on the filter context of the table. Thus, if you have product categories the measure will calculate the % change for each categorical value in the table.

  7. Hi, quick question. Why do I encounter this error while using the DAX measure above?

    ‘The True/False expression does not specify a column. Each True/False expressions used as a table filter expression must refer to exactly one column.’

    Thanks!

    • This is likely because you have selected a table ‘Table Name’ and not a column within a table ‘Table Name'[Column Name]

  8. Am I missing something? The formula old/new doesn’t do percent CHANGE, it just calculates percentage. Percent change is (New-old)/old.

Comments are closed.