Thanks for posting your data in a copy/pastable format! That makes it much easier for us to play with your data
So the first thing I would tell a PowerBI beginner is to consider A) whether Power BI is the right tool for the job and B) whether you want to create this % change value as a new column of data.
To the first point, Power BI is not Excel. It's great for displaying and slicing data, but not so good at adding data to tables that depend on previous rows. Now, I understand that you're likely trying to learn PowerBI here, so I won't let the fact that PowerBI doesn't work well with this particular example stop me from proceeding.
To the second point, PowerBI has a few options for using DAX to calculate values. It has measures and calculated columns. Calculated columns are added to the original data tables and are only updated when the data is refreshed. You also have to store the result, so if your tables are large, you can easily double their size in memory. They're useful for when you want to add static values for your data, or want to be able to filter the entire report by the result. These are what most new people gravitate towards because they're familiar.
Measures are more unique to DAX. They're a way to essentially ask "What is the value of [Measure] right now?" The important bit is the 'right now' part. They can recalculate on the fly, with different data, based on slicer selections and position in a table/graph. There are limitations, like you can't add a measure to a slicer, but depending on how you want to display your data, are more versatile. They're incredibly powerful, and are the tool that I would suggest for this problem.
On to your solution. The first thing to do is to create a new measure. I used this DAX code:
% Change =
VAR prevAmt = CALCULATE(SUM(Countries[Amount]), FILTER(ALLEXCEPT(Countries,Countries[Geography],Countries[Item]), Countries[Year]=SELECTEDVALUE(Countries[Year])-1))
RETURN
DIVIDE( SUM(Countries[Amount])-prevAmt, prevAmt, "-" )
This may look overwhelming at first, but let's break it down.
- The VAR section declares a variable (that I've called prevAmt) that I re-use twice in the actual calculation below. The RETURN statement just tells DAX that you're going back to the actual measure in question.
- The bottom section is pretty straightforward math. Take the SUM of the currently selected Amounts, subtract the previous amount, divide that by the previous amount.
- We have to use SUM here because measures only work on aggregations. Without you specifying, the measure doesn't know which value for [Amount] to use. When you only expect have one value, SUM is an easy way to get a numerical result.
- Also, if there's a divide by zero error, this returns a "-" instead of "infinity". You'll get this result when there is no previous year's data, or the [Amount] from a previous year is 0.
- Back to the VAR section. First the CALCULATE statement. This is how you calculate a result while changing the context. In this case, we're SUMming the amount, but changing the context to the previous year.
- After the comma is a FILTER statement. This defines the context filters we want to use when calculating the sum. Now, this FILTER is a bit more complex than I would have liked, but is the best way for your example.
- The ALLEXCEPT is a play on the syntax for ALL. When you say ALL(Table), it returns the entire table, ignoring any filters in place, including those from visual or report level filters! ALLEXCEPT returns all of the table in question, except it keeps the filters on columns specified.
- In this case, we wanted to get back all the data from Countries, but keep any applied filters on [Geography] and [Item]. This way we're not comparing the previous year's GDP and Population data from Europe, Chile, & Paris against the current year's GDP data for just Chile.
- Note that we didn't keep the filters applied to [Year] in the ALLEXCEPT, more on that next
- Now that we've defined the table we want to use for this calculation (from the ALLEXCEPT clause), we need to pick which year of data we want to use as a comparison. In this case, we want data from where the year is one less than the current year. Ergo, we want data where the [Year] is equal to the current year minus one. We use SELECTEDVALUE to return one value for year.
- SELECTEDVALUE returns one value, but only when there's one value available. In the context of the whole measure, there should only be one year's worth of data used to compare against the previous year's. Otherwise how could you calculate the percent change?
- Note that you can define a default value for SELECTEDVALUE if you wish, and that it defaults to blank.
And that's it! You've created a measure, and now you can add it to your visualization.
Here's my .pbix file using your data. I've created a few table style visuals that display the original data and the % change measure differently. I think the matrix is the best way to display data like this, but you may have a different purpose. We get back to one of my original questions: How do you want to display this data in the end? What is your vision?
Hopefully that helped you understand Power BI a little bit better. If you have further questions, or actually really do need the percent change as a calculated column, feel free to follow up here!
