Calculate Growth

[sban]

Hi,

I want to calculate the growth based on the theyear from the below data:

Market	Product	Account	Year	Amount
4616 European Area 2	product1	5970 Administration	2017	167.01
4616 European Area 2	product1	5970 Administration	2018	171.01
4616 European Area 2	product1	5970 Administration	2019	164
4616 European Area 2	product1	5920 Total Selling	2017	55001.36
4616 European Area 2	product1	5920 Total Selling	2018	53998.91
4616 European Area 2	product1	5920 Total Selling	2019	54202.36
4616 European Area 2	product1	9015 Total Advertising and Promotion	2017	11259.14
4616 European Area 2	product1	9015 Total Advertising and Promotion	2018	10929.46
4616 European Area 2	product1	9015 Total Advertising and Promotion	2019	10904.02
4616 European Area 2	product1	8555 Total Selling General and Administration	2017	87921.48
4616 European Area 2	product1	8555 Total Selling General and Administration	2018	86169.61
4616 European Area 2	product1	8555 Total Selling General and Administration	2019	86423.28
4616 European Area 2	product2	5920 Total Selling	2017	2599.27
4616 European Area 2	product2	5920 Total Selling	2018	2645.49
4616 European Area 2	product2	5920 Total Selling	2019	2646.93
4616 European Area 2	product2	9015 Total Advertising and Promotion	2017	487.32
4616 European Area 2	product2	9015 Total Advertising and Promotion	2018	492.32
4616 European Area 2	product2	9015 Total Advertising and Promotion	2019	476.15
4616 European Area 2	product2	8555 Total Selling General and Administration	2017	3086.59
4616 European Area 2	product2	8555 Total Selling General and Administration	2018	3137.81
4616 European Area 2	product2	8555 Total Selling General and Administration	2019	3123.08

[bullius]

Hi @sban,

So, you want to SUM the Amount for each year, then calculate the difference between each year?

[sban]

Yes, I want to calculate the sum of amount based on difference betwen the year like (2017- 2016) amount, based on the product, market and account.

[v-huizhn-msft]

Hi @sban,

Please create a new table by clicking "New Table" under Modeling on Home page, type the following formula. Please check more details of SUMMARIZE function here.

Table =
SUMMARIZE (
    'Fact',
    'Fact'[Market],
    'Fact'[Product],
    'Fact'[Account],
    'Fact'[Year],
    "sum of amount", SUM ( 'Fact'[Amount] )
)

Then create a calculated column to get the previous year's sum of amount based on product, market and account.

Previous year sum amount =
LOOKUPVALUE (
    'Table'[sum of amount],
    'Table'[Market], 'Table'[Market],
    'Table'[Product], 'Table'[Product],
    'Table'[Account], 'Table'[Account],
    'Table'[Year], 'Table'[Year] - 1
)

Finally, create a difference of amount total between this year and previous year(2017-2017, 2017-2018,2018-2019).

Difference = 'Table'[sum of amount]-'Table'[Previous year sum amount]

Please download the attachment file for detailed information.

Best Regards,
Angelia

[sban · Accepted solution]

Thanks much Angelia.. It really helped:)