Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi everyone,
I am having issue with calculating the average growth rate based on preceding years value, as I calculate the yearly growth rate based on the previous ones value, I want to calculate average growth rate based on the values from last recent three years for example if 2019's rate is 4%, 2020's rate is 3% and 2021's rate is 5% I want average rate of 12/3 = 4%.
Here is sample snap of my data:
Sales ID | Product Name | Date | Net Sales |
1 | Mercedes | 17/04/2019 | £11,500.00 |
2 | Belta | 17/04/2020 | £6,000.00 |
3 | Lamborghini | 28/04/2020 | £3,650.00 |
4 | Belta | 29/06/2020 | £12,500.00 |
5 | Jaguar | 29/07/2020 | £9,950.00 |
6 | Tacoma | 14/08/2020 | £5,500.00 |
7 | Jaguar | 14/08/2020 | £35,000.00 |
8 | Marill | 14/08/2020 | £45,500.00 |
9 | Tacoma | 24/09/2020 | £14,590.00 |
10 | Probox | 12/10/2020 | £16,500.00 |
11 | Probox | 18/10/2020 | £19,500.00 |
12 | Marill | 23/10/2020 | £65,500.00 |
13 | Probox | 27/10/2020 | £9,500.00 |
14 | Tacoma | 03/11/2020 | £3,500.00 |
15 | Probox | 10/11/2020 | £68,900.00 |
and here is the example of what i want to achieve including the formulas I expect to use, if correct:
Product | Year | Sales Growth Rate (SGR) | Average Growth Rate |
Mercedes | 2019 | 0 | (2019SGR + 2020SGR+2021SGR)/3 |
Mercedes | 2020 | (2020 sales - 2019)/2019 sales | (2019SGR + 2020SGR+2021SGR)/3 |
Mercedes | 2021 | (2021 - 2020)/2020 | (2019SGR + 2020SGR+2021SGR)/3 |
Belta | 2019 | 0 | (2019SGR + 2020SGR+2021SGR)/3 |
Belta | 2020 | (2020 sales - 2019)/2019 sales | (2019SGR + 2020SGR+2021SGR)/3 |
Belta | 2021 | (2021 - 2020)/2020 | (2019SGR + 2020SGR+2021SGR)/3 |
Tacoma | 2019 | 0 | (2019SGR + 2020SGR+2021SGR)/3 |
Tacoma | 2020 | (2020 sales - 2019)/2019 sales | (2019SGR + 2020SGR+2021SGR)/3 |
Tacoma | 2021 | (2021 - 2020)/2020 | (2019SGR + 2020SGR+2021SGR)/3 |
Thank you in advance.
Solved! Go to Solution.
hi @abbytank
you may
1) add a year column like this:
Year = YEAR([Date]),
2) plot a table measure with Product column, Year column and a measure like this:
SGR =
VAR _year = SELECTEDVALUE(TableName[Year])
VAR _sales = SUM(TableName[Sales])
VAR _salespy =
CALCULATE(SUM(TableName[Sales]), TableName[Year] = _year-1)
VAR _variance =
DIVIDE(_sales - _salespy, _salespy)
RETURN
IF(
ISBLANK(_salespy),
0,
_variance
)
For the average growth rate, not sure if that make sense to average a rate. It is more advisible to calculated the compund annual growth rate.
hi @abbytank
you may
1) add a year column like this:
Year = YEAR([Date]),
2) plot a table measure with Product column, Year column and a measure like this:
SGR =
VAR _year = SELECTEDVALUE(TableName[Year])
VAR _sales = SUM(TableName[Sales])
VAR _salespy =
CALCULATE(SUM(TableName[Sales]), TableName[Year] = _year-1)
VAR _variance =
DIVIDE(_sales - _salespy, _salespy)
RETURN
IF(
ISBLANK(_salespy),
0,
_variance
)
For the average growth rate, not sure if that make sense to average a rate. It is more advisible to calculated the compund annual growth rate.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
24 | |
20 | |
15 | |
13 |
User | Count |
---|---|
55 | |
48 | |
43 | |
19 | |
19 |