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,
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 |
Solved! Go to Solution.
Hi @sban,
So, you want to SUM the Amount for each year, then calculate the difference between each year?
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.
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
Thanks much Angelia.. It really helped:)
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 |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |