How to calculate growth rates for different markets in one table

[pfunda]

Hi there -

I have created a simple table with market data in Excel and now want to replicate it in Power BI. What is not so simple is to add growth rates... Although there are a some articles around this topic, I could not find a solution that fits to my challenge.

My goal is to have a table in Power BI that shows market data and growth rates YoY.

Region            2017            2018            %-Chg    2019             %-Chg   2022            %-Chg

Europe            18,937,982  18,578,319   -1.9%      18,137,558    -2.4%    18,217,401   +0.4%

RoW                  2,608,514    2,556,941    -2.0%       2,189,495    -14.4%   2,399,451    +9.6%

.

.

Total                41,888,456  41,509,052   -0.9%      40,406,489    +0.2%   40,406,486   +0.2%

Thanks in advance for any support on this.

[v-xicai · Accepted solution]

Hi @pfunda ,

You can create measure like DAX below, change some field or table names to make it effective, and try to use Matrix visual to display the result.

Growth rate =

VAR _previous = CALCULATE(SUM(Table1[Value]),FILTER(ALLSELECTED(Table1), 'Table1'[Region]=MAX(Table1[Region])&&Table1[Year] = MAX(Table1[Year]) -1))

VAR _current = CALCULATE(SUM(Table1[Value]),FILTER(ALLSELECTED(Table1),'Table1'[Region]=MAX(Table1[Region])&&Table1[Year] =MAX(Table1[Year])))

return

IF(_previous<>BLANK(),DIVIDE(_current-_previous,_previous),BLANK())

Best Regards,

Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[pfunda]

Hi Amy

Thanks so much for your proposed solution. It works and shows the growth rates by region.

May I ask for some additional advice...

a) The "Total" row is automatically calculated by Power BI and therefore the "Growth rate" measure does not work.  Is there a workaround?

b) There is no growth rate for the first year in a table. It is left blank (I have recognized this in your IF-formula). Is there a way to hide/exclude this empty column from the table?

Many thanks for your support.

Best,

Phil

[v-xicai]

Hi @pfunda ,

I am not sure what output you have currently, could you please share your output in screenshots for further analysis? You can also upload sample pbix to OneDrive and post the link here. Do mask sensitive data before uploading.

Best Regards,

Amy

[pfunda]

Hi Amy

This is an extract of the result:

Extract of table

...as 2018 is the first year in the table, there are no growth rates to show. Therefore the table shows an empty column. Can this be avoided?

Furthermore, the "Total" row is a subtotal of the Matrix table and therefore the measure formula does not work.

In the end, I know how to transform the data in the backend using Power Query to get to the result I want but it would be quite static and not as dynamic as if your DAX formulas are in use.

Many thanks,

Phil