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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
Super User
Super User

Hi, @Anonymous 

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

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


Go to My LinkedIn Page


View solution in original post

3 REPLIES 3
Jihwan_Kim
Super User
Super User

Hi, @Anonymous 

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

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


Go to My LinkedIn Page


Anonymous
Not applicable

@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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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