Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi I have a scenario where I have my data in the same column (unpivoted), but the data are labeled as different items, in consecutive years. I want to calculate the growth rate of each year by item all at once. Is it possible?
I created a dummy table as an example:
I want to create a 5th column that calculates the % change from one year to the next for GDP and Population seperately. Note that I unpivoted the original data. I'm still a Power BI beginner, so I am not sure if after unpivoting the data, I cannot call the 'Item' and/or 'Amount' column in a measure or a new column.
My end goal is to present visual charts the % change with slicers of geography, year, and item.
Please help! Thank you!
Solved! Go to Solution.
Thanks for that.
I created 2 columns.
One gets the previous year's value for the same Item and Geography
Previous Val = VAR _Item = Table1[Item] VAR _Year = Table1[Year] VAR _Geog = Table1[Geography] RETURN CALCULATE(SUM(Table1[Amount]), FILTER(Table1, Table1[Item] = _Item && Table1[Geography] = _Geog && Table1[Year] = _Year - 1))
The next shows the value as a percentage (Format this as a percentage)
%age = if (NOT ISBLANK(Table1[Previous Val]), (Table1[Amount] - Table1[Previous Val]) / Table1[Previous Val], 0)
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.
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!
Can you re-post the data (not a picture) and someone will help you out?
Hope this helps.
Geography | Year | Item | Amount |
Europe | 2011 | GDP | 5.93 |
Europe | 2012 | GDP | 5.63 |
Europe | 2013 | GDP | 4.01 |
Europe | 2014 | GDP | 8.86 |
Paris | 2011 | GDP | 3.29 |
Paris | 2012 | GDP | 2.46 |
Paris | 2013 | GDP | 2.34 |
Paris | 2014 | GDP | 6.97 |
Chile | 2011 | GDP | 6.98 |
Chile | 2012 | GDP | 6.89 |
Chile | 2013 | GDP | 7.53 |
Chile | 2014 | GDP | 6.22 |
Europe | 2011 | Population | 6.05 |
Europe | 2012 | Population | 9.64 |
Europe | 2013 | Population | 2.85 |
Europe | 2014 | Population | 3.55 |
Paris | 2011 | Population | 7.70 |
Paris | 2012 | Population | 7.75 |
Paris | 2013 | Population | 3.80 |
Paris | 2014 | Population | 6.55 |
Chile | 2011 | Population | 1.19 |
Chile | 2012 | Population | 0.30 |
Chile | 2013 | Population | 6.54 |
Chile | 2014 | Population | 8.15 |
Thanks for that.
I created 2 columns.
One gets the previous year's value for the same Item and Geography
Previous Val = VAR _Item = Table1[Item] VAR _Year = Table1[Year] VAR _Geog = Table1[Geography] RETURN CALCULATE(SUM(Table1[Amount]), FILTER(Table1, Table1[Item] = _Item && Table1[Geography] = _Geog && Table1[Year] = _Year - 1))
The next shows the value as a percentage (Format this as a percentage)
%age = if (NOT ISBLANK(Table1[Previous Val]), (Table1[Amount] - Table1[Previous Val]) / Table1[Previous Val], 0)
That is absolutely what I needed. Thank you very much! This is super helpful.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |