Forum Discussion

sban's avatar
sban
Regular Visitor
8 years ago
Solved

Calculate Growth

Hi,

 

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

MarketProductAccountYearAmount
4616 European Area 2product15970 Administration2017167.01
4616 European Area 2product15970 Administration2018171.01
4616 European Area 2product15970 Administration2019164
4616 European Area 2product15920 Total Selling201755001.36
4616 European Area 2product15920 Total Selling201853998.91
4616 European Area 2product15920 Total Selling201954202.36
4616 European Area 2product19015 Total Advertising and Promotion201711259.14
4616 European Area 2product19015 Total Advertising and Promotion201810929.46
4616 European Area 2product19015 Total Advertising and Promotion201910904.02
4616 European Area 2product18555 Total Selling General and Administration201787921.48
4616 European Area 2product18555 Total Selling General and Administration201886169.61
4616 European Area 2product18555 Total Selling General and Administration201986423.28
4616 European Area 2product25920 Total Selling20172599.27
4616 European Area 2product25920 Total Selling20182645.49
4616 European Area 2product25920 Total Selling20192646.93
4616 European Area 2product29015 Total Advertising and Promotion2017487.32
4616 European Area 2product29015 Total Advertising and Promotion2018492.32
4616 European Area 2product29015 Total Advertising and Promotion2019476.15
4616 European Area 2product28555 Total Selling General and Administration20173086.59
4616 European Area 2product28555 Total Selling General and Administration20183137.81
4616 European Area 2product28555 Total Selling General and Administration20193123.08
  • sban's avatar
    sban
    8 years ago

    Thanks much Angelia.. It really helped:)

     

4 Replies

  • bullius's avatar
    bullius
    Helper V
    8 years ago

    Hi sban,

     

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

    • sban's avatar
      sban
      Regular Visitor
      8 years ago

      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's avatar
        v-huizhn-msft
        Microsoft Employee
        8 years ago

        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