Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreShape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.
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.
User | Count |
---|---|
92 | |
88 | |
88 | |
79 | |
49 |
User | Count |
---|---|
156 | |
145 | |
105 | |
72 | |
55 |