Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
Anonymous
Not applicable

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

@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

YoY Revisited.png

View solution in original post

12 REPLIES 12
Sean
Community Champion
Community Champion

arify
Microsoft Employee
Microsoft Employee

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"
Anonymous
Not applicable

@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
Microsoft Employee
Microsoft Employee

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

Anonymous
Not applicable

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

@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

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. 

Anonymous
Not applicable

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

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
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.