cancel
Showing results for
Did you mean:
Helper II

## Group by with percentage growth

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.

1 ACCEPTED SOLUTION
Community Champion

@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

12 REPLIES 12
Community Champion
Microsoft

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
Helper II

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

Microsoft

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

Helper II

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.

Community Champion

@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

Helper I

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!

Helper I

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.

Helper II

Microsoft

@Jasel

You can get correct result with following measure:

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

Regards,

Microsoft

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.

Announcements