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: Market Product Account Year Amount 4616 European Area 2 product1 5970 Administration 2017 167.01 461...
  • sban's avatar
    sban
    8 years ago

    Thanks much Angelia.. It really helped:)

     

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

    • sban's avatar
      sban
      Regular Visitor
      8 years ago

      Thanks much Angelia.. It really helped:)