I am trying to add a simple formula to get the average price of a product based on the orders and sales per day within a cross tab.
When I add the following formula it appears to apply the same value to every column(date) even though the product and sales values for each date is changing.
This only seams to display properly in the totals column when I use
if sum(ProductOrders)<>0 then sum(TotalSales)/sum(ProductOrders)
however it then puts this values in all the columns because its summing all the data. I am setting the field summary to be averaged.
is there a way to show the average price of orders for the day properly in each column and the totals column as well? or do I have to do this in a Stored Procedure.