cancel
Showing results for
Did you mean:
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:

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

I'd appreciate any help.

1 ACCEPTED SOLUTION
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.

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.

3 REPLIES 3
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.

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.

New Member

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

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/

Announcements

#### Welcome to the User Group Public Preview

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

#### Microsoft Business Applications Summit sessions

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

#### Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors