Forum Discussion
How to get certain data from a pivot table
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 yoursAlso, 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))
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))
- 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
JasonGlad this helps & Thanks for providing feedback
Happy new year too. All the best...
