Here’s a simple dataset showing the Date of sale, Customer Name and Sales amount.
| Date | Customer Name | Sales amount |
| 12-03-2017 | A | 1 |
| 12-03-2017 | A | 2 |
| 12-03-2017 | A | 3 |
| 12-03-2017 | B | 4 |
| 12-03-2017 | B | 5 |
| 12-03-2017 | B | 6 |
| 12-03-2017 | B | 7 |
| 12-03-2017 | B | 8 |
| 13-03-2017 | A | 1 |
| 13-03-2017 | A | 1 |
The objective is to determine Customer wise:
- Highest revenue; and
- Date on which that highest revenue was earned
The expected result is
| Row Labels | Highest revenue | Date of highest revenue |
| A | 6 | 12-Mar-2017 |
| B | 30 | 12-Mar-2017 |
| Grand Total | 36 |
So, for A, revenue earned on March 12, 2017 is 6 and on March 13, 2017 is 2. Since the higher of the two is 6, that is the result which should appear in the Pivot Table.
I have solved this problem with the PowerPivot. You may download my solution workbook from here.
In a Pivot Table, compute highest revenue earned on any day from each customer and the date thereof
{ 0 Comments }