Forum Discussion

Jason_Bradbury's avatar
Jason_Bradbury
Copper Contributor
Dec 31, 2022

How to get certain data from a pivot table

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

  • 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))

     

  • Lorenzo's avatar
    Lorenzo
    Silver Contributor

    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))

     

    • Jason_Bradbury's avatar
      Jason_Bradbury
      Copper Contributor
      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
      • Lorenzo's avatar
        Lorenzo
        Silver Contributor

        Glad this helps & Thanks for providing feedback

        Happy new year too. All the best...

Resources