Results 1 to 4 of 4

Thread: Pivot tables with diferent Value Field settings from the Subtotal field

  1. #1

    Pivot tables with diferent Value Field settings from the Subtotal field



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello,

    I am having trouble with a pivot feature and I have Excel 2007. My raw data for example has different entries for a same date, When building the Pivot, I need to check the Min of each date. So when I change the Value field setting to Min, the Subtotal will also be the Min (I need the Sum of all the Min). When I try a calculated formula field in the Pivot it will not return the absolute minimum data for each date.

    Please help!

    Andres Benavides

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,268
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    I don't believe that you're able to do this with PivotTables. With PowerPivot yes, but you need a minimum of Excel 2010 for that.

    If you can modify your data source (add columns to it) then you can do it that way in 2007 though. You just need to add a column that works out the MIN for any given date, returning 0 if the row is not the minimum. Then you could use the SUM function in the PivotTable. Does that make sense?
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    Hey Ken, thanks for the quick response.

    Well that kind of helps I usually add some help columns to the source data but in this case I am kind of lost with the formula because the MIN needs to be from the start time and do a cross check with the person who logged in.

    Basically what I have in my data source would be dates in column A, worker name in column B, start time in column C. It may have more that one row per worker per day, that is why I am interested in the minimum per worker per day and compare it to a table with the target start time for each. I don't know if I explained myself correctly, I could try to upload some dummy data later today to recreate my problem.

    Thanks again Ken

  4. #4
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,268
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Dummy data would definitely help.
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •