Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi! How can I calculate measure in pivot table - Sales Vs Prev. sales, % ( there could be more years, and i have Calendar Master if it helps)?
2016 | 2017 | 2018 | ||||
---|---|---|---|---|---|---|
Sales | Sales Vs Prev. sales, % | Sales | Sales Vs Prev. sales, % | Sales | Sales Vs Prev. sales, % | |
dec | 10 | 100% | 12 | 120% | 10 | 83% |
jan | 9 | 100% | 6 | 66% | 8 | 133% |
feb | 15 | 100% | 16 | 106% | 18 | 113% |
Hi,
you can use Before() and Alt() functions for this.
Sales = Sum(Sales)
Sales vs Prev. sales, % = Alt(Sum(Sales)/Before(Sum(Sales)), 1)
Before wil calculate Sum(Sales) using previous dimension value (previous year in your case), but in the first column it will return null (because there's no previous value), therefore you need to combine it with Alt(), witch returns first valid value from a list of parameters.
Hope this helps.
Juraj
Hi,
you can use Before() and Alt() functions for this.
Sales = Sum(Sales)
Sales vs Prev. sales, % = Alt(Sum(Sales)/Before(Sum(Sales)), 1)
Before wil calculate Sum(Sales) using previous dimension value (previous year in your case), but in the first column it will return null (because there's no previous value), therefore you need to combine it with Alt(), witch returns first valid value from a list of parameters.
Hope this helps.
Juraj
Thank you for so fast answer!