cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MSC
Helper III
Helper III

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. For me, the difficulty is also in the condition that the function should pay attention to each product's first year in the table and it thus should return blank in these years.

 

In general, my data looks like this.

ProductDateSales
A31.12.19                     4.242
A31.12.18                     2.455
A31.12.17                     1.543
A31.12.16                        416
A31.12.15                        264
B31.12.19                     6.363
B31.12.18                     4.683
B31.12.17                     2.315
B31.12.16                     1.624
B31.12.15                        396
C31.12.19                     9.954
C31.12.18                     6.524
C31.12.17                     3.425
C31.12.16                     2.641
C31.12.15                     1.256

 

 

You can find the excel file here.

https://github.com/MSC791/Growth_Table.git 

 

Thank you very much in advance for your support and suggestions!

1 ACCEPTED SOLUTION
camargos88
Super User III
Super User III

@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)


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



View solution in original post

16 REPLIES 16
camargos88
Super User III
Super User III

@MSC ,

 

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

 

So you will ignore the selected date and get all the date values to calculate the average.



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



@camargos88I've used this function:

Average Measure =

VAR CustomerGroup_ =

    DISTINCT(Data_Table[Customer])

RETURN

    CALCULATE(AVERAGE(Data_Table2[Growth]), Data_Table[Category] IN CustomerGroup_, ALL(Data_Table[Customer]) )

Thanks a lot for your inputs and your help!

camargos88
Super User III
Super User III

@MSC ,

 

Are you filtering the values by this table ? So that's why the average is wrong ?



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



@camargos88I'm filterting the values by the data table where I have the products, the categories, and the sales.

MSC
Helper III
Helper III

@camargos88@Ashish_Mathur  I think I've found the source of the problem: In the pbix I'm working with, the date column doesn't have the calendar symbol in front of it... even though the settings seem to be correct. The date type is date and format is dd.mm.yyy

 

How can I change this?

 

0date.JPG

 

00date.JPG

 

0date2.JPG

 

Hence, I think this causes the function and especially the term ".. && [Date]..." not to work as intended.

MSC
Helper III
Helper III

I just realized that the problem might be that there's relationship between the table with the data and the date table. @camargos88 How can I integrate the date table in your DAX function?

MSC
Helper III
Helper III

@Ashish_MathurI've uploaded a little demo dashboard with the DAX functions provided by @camargos88 which shows the results I'd like to get. If you've a measure solution, you're welcome.
However, the functions don't work with my real dataset even though the underlying data has the same structure as the demo data. At the moment, I'm trying to figure out why this happens.

Ashish_Mathur
Super User III
Super User III

Why do you want a calculated column solution?  Why not a measure?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi @Ashish_Mathur !

Initially, I preferred a column solution because I'd found a workaround for a similar problem which was based on calculated columns.

However, if there's a measure solution which returns the average category growth rate of the product I selected, this would be fine as well.

camargos88
Super User III
Super User III

@MSC ,

 

Try this measure:

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

 

Check the attached file.

 



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



@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.

MSC
Helper III
Helper III

@camargos88I've just uploaded on expanded version of the file.

camargos88
Super User III
Super User III

@MSC ,

Can you provide the input data and the desired output ? I can work on it.



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



@camargos88Yes, of course. Thanks. I'll notify you as soon as the prepared data is uploaded on github.

camargos88
Super User III
Super User III

@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)


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



View solution in original post

@camargos88Cool! Thank you very much for your quick response! I'll mark it as the solution. However, I've a follow-up question you might be able to help me with. Imagine product A and B belong to product category Alpha and C belongs to Beta.
Now, I'd like a function to return the average growth rate of Alpha, when I filter for product A on my dashboard. Do you know how this can be done?

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors