Forum Discussion

MSC's avatar
MSC
Icon for Helper III rankHelper III
5 years ago
Solved

Calculate sales growth using calculated column

Hi everyone!   I'd like to create a calculated column which returns the growth rate of my product sales. I've already managed to do with by means of measures but failed with a calculated column. Fo...
Need Help
Show and Tell
  • camargos88's avatar
    camargos88
    5 years ago

    MSC ,

     

    Try this code for a new calculated column:

    Growth = 
VAR _lastDate = MAXX(FILTER('Table', 'Table'[Product] = EARLIER('Table'[Product]) && [Date] < EARLIER('Table'[Date])), 'Table'[Date])
VAR _lastValue = CALCULATE(SUM('Table'[Sales]), FILTER('Table', 'Table'[Product] = EARLIER('Table'[Product]) && [Date] = _lastDate))

RETURN IF(_lastValue <> BLANK(),  ('Table'[Sales] - _lastValue) / _lastValue)
camargos88's avatar
camargos88
Icon for Community Champion rankCommunity Champion
5 years ago

MSC ,

 

Try this measure:

_Average = CALCULATE(AVERAGE('Table'[Growth]), ALL('Table'[Product]))

 

Check the attached file.

 

MSC's avatar
MSC
Icon for Helper III rankHelper III
5 years ago

camargos88  Thank you very much for your help so far! I really appreciate it!

 

Interestingly, the measure you suggested works perfect on my demo dashboard but there seems to be a little problem in my real dataset. There, the average measure returns the normal growth rates of the selected item but not the average growth rate of the iteam category.

 

I've to figured that out and will come back to you tomorrow.