I have a simple microsoft excel table that shows sales and its date:
| date | notes | sales |
|---|---|---|
| 1/1/2024 | laptop | 10 |
| 1/2/2024 | phone | 5 |
| 2/2/2024 | laptop | 15 |
| 5/2/2024 | laptop | 20 |
| 6/2/2024 | phone | 5 |
| Total | 55 |
i want to add a pivot table to show accumulated sales as a running total of the sales:
i added the “date” filed to rows area, and the sales to the values area, and set the “show value as” in the value field settings to “Running total in”, and “base field” to date ,
the result the following table
| date | Sum of sales |
|---|---|
| 1/1/2024 | 10 |
| 1/2/2024 | 15 |
| 2/2/2024 | 30 |
| 5/2/2024 | 50 |
| 6/2/2024 | 55 |
i also want to show the notes filed so added it to the rows area after the date field, the result the following table:
| date | notes | Sum of sales |
|---|---|---|
| 1/1/2024 | laptop | 10 |
| 1/2/2024 | phone | 5 |
| 2/2/2024 | laptop | 25 |
| 5/2/2024 | laptop | 45 |
| 6/2/2024 | phone | 10 |
The Problem now is the pivot table doesn’t calculate correct values of the running total as you can see it seems that the pivot table groups the values based on changes in the “notes” field, it groups the sales with “laptop” and calculate it’s own running total and group the sales with “phone” and calculate it’s own running total.
i want to show the running total of all the sales Regardless of data in the notes filed.
i expected to get the running total of all the sales Regardless of data in the notes filed.