Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
I have a weird calculation I need to make to calculate growth percentage. The difficulty is how the data is stored:
Item Year Revenue
X 2015 500
X 2016 605
Y 2015 400
Y 2016 805
Z 2015 205
Z 2016 405
It looks like I need to use GroupBy instead of Summarize but I just haven't been able to figure out the right way.
End result would be:
Item | Revenue Growth |
X | 21% |
Y | 101.25% |
Z | 97.56% |
Any assistance is greatly appreciated.
Solved! Go to Solution.
@Anonymous You'll need a Calendar Table (if you don't have one go here )
Total Revenue = SUM('Table'[Revenue]) YTD Total Revenue = TOTALYTD([Total Revenue], CalendarTable[Date]) PY Total Revenue = CALCULATE([YTD Total Revenue], PREVIOUSYEAR(CalendarTable[Date])) YoY Change = [YTD Total Revenue] - [PY Total Revenue] YoY % = DIVIDE([YoY Change], [PY Total Revenue], 0)
Then it will NOT matter how your table is sorted etc...
I added 2014 to be double 2015 so you get negative for 2015
@Anonymous Look at my response here...
https://community.powerbi.com/t5/Desktop/Column-Visual-YoY-Comparison/m-p/33783#U33783
You can use GroupBy with 2 new columns:
Note: This assumes growth percentage is always positive 😕
Actually, it's fine. You can go to Advanced Editor and change List.Max with List.Last, and List.Min with List.First 🙂
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Column1"}, {{"Max", each List.Last([Column2]), type number}, {"Min", each List.First([Column2]), type number}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Percentage", each 100.0 * ([Max] - [Min]) / [Min]) in #"Added Custom"
@arify - I was able to use your solution as a guidline to figure it out. I don't think you saw that my data had two columns complicating the way you suggested the grouping. Both Item and Year have data that repeats.
I have a Item, and a Year that need to be grouped by as they have duplicates so to speak:
Item Year Revenue
X 2015 500
X 2016 605
Y 2015 400
Y 2016 805
Z 2015 205
Z 2016 405
I ended up duplicating the tables and created a 2015 table and a 2016 table. I then created a calculated column with the formulat you supplied.
I won't mark this as answered as I'm sure there is a better way. I really appreciate everybody's input and different options listed.
As long as 2015 comes before 2016 for each Item value, you can ignore the Year column in my solution.
I see. The data is sporadic as best. Item and Year are not ordered in any manor, and there are more columns in the table that I did not mention to avoid further confusion.
So if I ordered by item, then year your saying your solution would work without using a year? Let me give it a try.
@Anonymous You'll need a Calendar Table (if you don't have one go here )
Total Revenue = SUM('Table'[Revenue]) YTD Total Revenue = TOTALYTD([Total Revenue], CalendarTable[Date]) PY Total Revenue = CALCULATE([YTD Total Revenue], PREVIOUSYEAR(CalendarTable[Date])) YoY Change = [YTD Total Revenue] - [PY Total Revenue] YoY % = DIVIDE([YoY Change], [PY Total Revenue], 0)
Then it will NOT matter how your table is sorted etc...
I added 2014 to be double 2015 so you get negative for 2015
hello Sean,
first, thanks for the solution, but when i applied it to our data, it is always right only for current year's data. which caused the PY column is blank. i created all the measures just like your suggestions, and also created the Calendar first.
but since our transaction table have only month & year (grouped by monthly transaction), i created new column TransactionDate = DATE('TransactionTable'[year],'TransactionTable'[month],1) which related to column 'Calendar'[Date].
i searched for this solution (yearly growth) everywhere with no result. until yours showed up, and i'm really thank you for this.
but it seems that i have to keep searching, so any help would be very appreciate, thanks!
Turn out that your solution is the RIGHT solution Sean!
My mistake is, i took the year column from TransactionTable not from Calendar.
I've just tried to set the year column from Calendar, then.. VOILA! the numbers appears just like magic! 😄
Thanks again Sean!
I would do this in a query.
If you pull the table in twice, filtering one to 2015 and again to 2016. Then merge off of unique ID (item). Then you'd have a table with these columns.
Item, 2015, 2016, Revenue 2015, Revenue 2016
Then you can add a calculated column that divides revenue 2015 by revenue 2016.
Thanks for the reply Beckham. Sadly the data source is an excel spreadsheet and loading it into something like a SQL database is not an option.
@Anonymous
You can get correct result with following measure:
Percentage_Growth = CALCULATE ( MAX ( Table1[Revenue] ) - MIN ( Table1[Revenue] ) ) / CALCULATE ( MIN ( Table1[Revenue] ) )
Regards,
Simon_Hou wrote:
You can get correct result with following measure:
Percentage_Growth = CALCULATE ( MAX ( Table1[Revenue] ) - MIN ( Table1[Revenue] ) ) / CALCULATE ( MIN ( Table1[Revenue] ) )
As I mentioned in my reply, that won't handle the negative growth cases correctly.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
113 | |
89 | |
84 | |
76 | |
64 |
User | Count |
---|---|
136 | |
111 | |
98 | |
97 | |
92 |