cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jomba123
New Member

Calculate percentage growth in same column (and same group)

I would like to calculate the percentage growth of the population from 2011-2020 for each age-group.

I created the following calculated table:

 

screen.jpg

 

Now i would like to have a extra column with the %-growth for each age-group.

I'd appreciate any help.

1 ACCEPTED SOLUTION
Jihwan_Kim
Community Champion
Community Champion

Hi, @jomba123 

I am not sure if I understood your question correctly.

I created a simplified version of your sample.

Please check the below picture and the sample pbix file's link down below.

It is for creating a new column. The formula is for finding the growth percentage that compares to the previous year. In this case, the previous year of 2020 in the table is 2011. 

 

Picture3.png

 

Age Group Growth Percent CC =
VAR currentperiod = 'Table'[Year]
VAR previousperiod =
MAXX ( FILTER ( 'Table', 'Table'[Year] < currentperiod ), 'Table'[Year] )
VAR previousperiodvalue =
CALCULATE (
CALCULATE ( SUM ( 'Table'[Population] ) ),
FILTER (
'Table',
'Table'[Age] = EARLIER ( 'Table'[Age] )
&& 'Table'[Year] = previousperiod
)
)
RETURN
DIVIDE ( 'Table'[Population] - previousperiodvalue, previousperiodvalue )
 
 

Hi, My name is Jihwan Kim.


If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.


Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

View solution in original post

3 REPLIES 3
Jihwan_Kim
Community Champion
Community Champion

Hi, @jomba123 

I am not sure if I understood your question correctly.

I created a simplified version of your sample.

Please check the below picture and the sample pbix file's link down below.

It is for creating a new column. The formula is for finding the growth percentage that compares to the previous year. In this case, the previous year of 2020 in the table is 2011. 

 

Picture3.png

 

Age Group Growth Percent CC =
VAR currentperiod = 'Table'[Year]
VAR previousperiod =
MAXX ( FILTER ( 'Table', 'Table'[Year] < currentperiod ), 'Table'[Year] )
VAR previousperiodvalue =
CALCULATE (
CALCULATE ( SUM ( 'Table'[Population] ) ),
FILTER (
'Table',
'Table'[Age] = EARLIER ( 'Table'[Age] )
&& 'Table'[Year] = previousperiod
)
)
RETURN
DIVIDE ( 'Table'[Population] - previousperiodvalue, previousperiodvalue )
 
 

Hi, My name is Jihwan Kim.


If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.


Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

View solution in original post

@Jihwan_Kim exactly what i was looking for. Thank you very much!

gdarakji
Resolver III
Resolver III

The best way to do it is using time intelligence functions but for time intelligence functions to work, you need to edit your model a bit. You need to create a data table, update the dates in your existing table and relate the dates table with your existing table. Then you can use the PARALLELPERIOD to jump 9 years back. A similar example is shown on https://www.mssqltips.com/sqlservertip/6424/calculate-percentage-growth-over-time-with-power-bi/

 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors