Get sum of one column, max of another in pivot table

I have 4 columns of data.

DateID: integer representing date
HoldingDate: Date and time info
Units: -1, 0, or 1
NetProfit: real number

I want to put this in a pivot table so that I can see the summed NetProfit for a DateID, and see the Units value for the chronologically last HoldingDate value for each DateID.

I've highlighted the values I want in yellow in the attached file to make this more clear. I'm trying to model this problem in a pivot table before recreating in a cube in SQL Server Management Studio.

Thanks for the help!

Hi,

Just wanted to understand your question correctly:

1. Do you want to know the summation of net profits with the final no of units on a particular day
2. Do you want to know the summation of net profits with the final no of units for a particular date id

If the question is 1, then just change the value field settings for "Sum of Units" to "Max of Units"
If the question is 2, then remove Holding date from the pivot and add a column in the raw data which calculates the final number for a particular Date ID. This can be simply done by checking the max date for a particular Date ID, if it is true then pick up the value or else make it 0.

Hope this helps.

Regards
Roshan

Correct me if I'm missing what you are trying to say, but does holding date and time have to be present?

Because it seems as though you could just remove Holding Date from the Row Labels?

Let me clarify with a simple example

DateID, HoldingDate, Units, NetProfit,
1, 6/1 6am, 1, 100
1, 6/1 3 pm, -1, 50
2, 6/2 6 am, -1, 200
2, 6/2 4 pm, -2, 400

For DateID=1, I want the last Units value for the DateID, and the sum of NetProfit for the DateID. For DateID=1, I want Units=-1, and NetProfit=150

Similarly for DateID=2, I want Units=-2, and NetProfit=600.

Does this add clarity?

Thanks

ps - is there a better way to embed sample data in a post?

@NBVC

Good idea.

Is there a way to do it without a helper column? The source data lives in a SQL Server database with milliions of rows, and the DBA will no way let me add a column to that table. I want to solve this problem eventually with a cube, and am modeling it with a pivot table first to see if possible.

Originally Posted by NBVC

If your DateID's are grouped, then you can add a helper column to the main database with formula in E2:

=IF(A3=A2,"",C2)

copied down

Then expand the PT source data to include the last column and add the Sum of the column to your Sum labels.

If you want the corresponding date, then add the HoldingDate to the Sum field and choose Max.