Report to show P&L total

[BradC]

I have set up a simple database to track stock market portfolios. I have everything set up, but I am having trouble with a report.

It currently lists trade date, type (buy or sell), quantity, amount and cost per share. I then total each individual stock so that I can see what my current balance is for the following: stock quantity, stock amount, average cost per stock and if all stocks are sold my Profit or Loss. See attached image.

I then want to take all the profit or loss results from various stock holdings to give me the total for the portfolios. Just don't know how to do that part.

Thanks for any help.

 

[apr pillai]

If I understood you correctly you can use the

=DSUM("FieldName","Table/QueryName"[I],"Criteria"[/I])

Function to bring the total into a TextBox on the Report, where you have shown the sample Profit/Loss value. Use Criteria parameter only if you need some filtering of data otherwise omit that.

 

[BradC]

Thanks apr pillai

I will give that a try.

Did get it working using the following:

=IIf([ClosingQuantity]>0 And [SumOfAmount]<0,[SumOfAmount]*-1,(IIf([ClosingQuantity]=0,[SumOfAmount]*-1,0)))