I have this table
and I want to get the latest -by date- "latest Sum" value of every "From".
I expect something like :
2018-02-03 | person1 | 9200
2018-02-05 | person2 | 2600
I created a pivot table but apparently I made something wrong. Here it's what I got
I want it to show only the latest date
Best Answer
Here is a step-by-step guide describing one possible way in which to obtain the result that you require:
I've started with your current setup:
A table containing the relevant data, and a pivot table created using the default options, with the
From
andLatest Sum
fields as Row Labels, and theDate
field as a Value, with the Value Field Setting set to summarise theDate
field using theMax
function:This configuration yields a pivot table as shown in your question:
Under the PivotTable Tools contextual tab, click on Design and then on Subtotals and select Do Not Show Subtotals:
Now click on Grand Totals and select Off for Rows and Columns:
Now click on Report Layout and select Show in Tabular Form:
Select the filter arrow beside the
Latest Sum
field, then select Value Filters and finally Top 10...:Configure the Value Filter as follows:
Final Result