cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Jasel
Helper II
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
X21%
Y101.25%
Z97.56%

 

Any assistance is greatly appreciated.

1 ACCEPTED SOLUTION
Sean
Community Champion
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

YoY Revisited.png

View solution in original post

12 REPLIES 12
Sean
Community Champion
Community Champion

You can use GroupBy with 2 new columns:

Untitled.png

Untitled.png

 

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.

Sean
Community Champion
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

YoY Revisited.png

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

 

PowerBI.png

 

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!

Beckham
Advocate II
Advocate II

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.

@Jasel

 

You can get correct result with following measure:

 

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

234234.png

 

Regards,


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.

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors