Group by with percentage growth

[Jasel]

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.

[Sean]

@Jasel Look at my response here...

https://community.powerbi.com/t5/Desktop/Column-Visual-YoY-Comparison/m-p/33783#U33783

[arify]

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"

[Jasel]

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

[arify]

As long as 2015 comes before 2016 for each Item value, you can ignore the Year column in my solution.

[Jasel]

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.

[Sean · Accepted solution]

@Jasel 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

[biki]

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!

[biki]

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!

[Beckham]

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. 

[Jasel]

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.

[v-sihou-msft]

@Jasel

You can get correct result with following measure:

Percentage_Growth = 
CALCULATE ( MAX ( Table1[Revenue] ) - MIN ( Table1[Revenue] ) )
/ CALCULATE ( MIN ( Table1[Revenue] ) )

Regards,

[arify]

---

Simon_Hou wrote:

@Jasel

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.