How to get certain data from a pivot table

[Jason_Bradbury]

Hi all,

I have a table that I'm using to track weights of fish, the data goes back a couple of years and contains, Name, date of capture, weight etc. What I can't work out is how to create a pivot table that will show me the name and the weight at the last capture.

I kind of got it to work by adding date to the values field and setting to max and adding name and weight in rows but this looks really messy in a chart. Ideally I want Name in rows and weight as value but this always shows the sum and not the latest weight.

Many thanks for reading.

Jason

[#2]

@Jason_Bradbury 

Please provide your workbook.

[#3 · Accepted solution]

Hi @Jason_Bradbury 

Hi Jason

I do support @Detlef_Lewin request. When you don't provide a sample representative workboook:
#1 Contributors to this forum spend time creating dummy data that already exist
#2 There's always a risk the fictitius sample + the setup don't reflect yours

Also, you didn't provide your version of Excel nor your OS (Windows, MacOS). For next time(s) please have a look to Welcome to your Excel discussion space!

Couple of ways

#1 Add a Helper column to your Source table (in grey above), where when the [Date] is the most recent one you return the [Weight] otherwise 0. Then, with a classic Pivot table do a SUM of [Last W.] in the Values area. Formula in G4:

=IF([@Date] = MAXIFS([Date], [Name],[@Name]), [@Weight], 0)

#2 If you run Excel >/= 2013 on Windows, load your Source table to the Data Model (Power Pivot) and setup a DAX measure that does something similar without having to add a Helper column to your table. In attached file measure [Last Weight] is:

=VAR MaxDate = MAX(Table1[Date])
RETURN
CALCULATE(SUM(Table1[Weight]), FILTER(Table1, Table1[Date]=MaxDate))

[#4]

Hi,

Firstly thanks to you both for your replies, I apologise for not attaching a sample of my workbook, this would have made it much easier!

@L z, thank you for the detailed response and example, this is exactly what I was looking for and it would have taken me a long time to figure this out! once again much appreciated.

Happy new year

Jason

[#5]

Glad this helps & Thanks for providing feedback

Happy new year too. All the best...