Forum Discussion
Helper IIICalculate 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.
| Product | Date | Sales |
| A | 31.12.19 | 4.242 |
| A | 31.12.18 | 2.455 |
| A | 31.12.17 | 1.543 |
| A | 31.12.16 | 416 |
| A | 31.12.15 | 264 |
| B | 31.12.19 | 6.363 |
| B | 31.12.18 | 4.683 |
| B | 31.12.17 | 2.315 |
| B | 31.12.16 | 1.624 |
| B | 31.12.15 | 396 |
| C | 31.12.19 | 9.954 |
| C | 31.12.18 | 6.524 |
| C | 31.12.17 | 3.425 |
| C | 31.12.16 | 2.641 |
| C | 31.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!
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)
16 Replies
camargos88Community Champion
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)
MSCcamargos88Cool! 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?
- camargos88
MSC ,
Try this measure:
_Average = CALCULATE(AVERAGE('Table'[Growth]), ALL('Table'[Product]))Check the attached file.
- MSC
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.
- camargos88
MSC ,
Can you provide the input data and the desired output ? I can work on it.
- MSC
camargos88Yes, of course. Thanks. I'll notify you as soon as the prepared data is uploaded on github.
- MSC
camargos88I've just uploaded on expanded version of the file.
- Ashish_Mathur
Super User
Why do you want a calculated column solution? Why not a measure?
- MSC
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.
- MSC
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. - MSC
camargos88Ashish_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?
Hence, I think this causes the function and especially the term ".. && [Date]..." not to work as intended.
- MSC
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?
- camargos88
- MSC
camargos88I'm filterting the values by the data table where I have the products, the categories, and the sales.
- camargos88
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.
- MSC
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!
