1 Reply Latest reply on Dec 29, 2018 6:13 PM by Rodrigo Calloni

How to show Sum of Sales broken down by Order Date Month for 2017 ,Show row grand totals for 2017 nearby,show only grand totals for 2016 nearby and % Change from 2016 to 2017

How to show Sum of Sales broken down by Order Date Month for 2017 ,Show row grand totals for 2017 nearby,show only grand totals for 2016 nearby and % Change from 2016 to 2017

• 1. Re: How to show Sum of Sales broken down by Order Date Month for 2017 ,Show row grand totals for 2017 nearby,show only grand totals for 2016 nearby and % Change from 2016 to 2017

Hello Manoj

Here how you can achieve this:

1- Create 2 calculations to get the sales for 2017 and 2016:

2016 Sales

IF DATEPART('year', [Order Date])=2016 THEN [Sales]

END

2017 Sales

IF DATEPART('year', [Order Date])=2017 THEN [Sales]

END

2- Create a calculation to generate the % change. For that you need to subtract the 2017 sales from 2016 sales to get how much more or less the change was. Then you divide that by the 2016 Sales which is your baseline for comparison

% Change

(SUM([2017 Sales])-SUM([2016 Sales]))/SUM([2016 Sales])

3- On the Analysis menu you can activate the Totals > Show Row Grand Totals

4- Then I tested all calculations in a table view:

The workbook is attached and it also has a dual axis timeline to show the difference over time.

I hope it helps

Rodrigo

1 of 1 people found this helpful