I am building my first workbook that compares actuals to projections. I finally figured out how to do this using this post:
Because my projections are only at the month level and my data is at the detail level, I include the applicable month projection number with each row of data, by joining my detailed data with the projection data. So the fields in my query are like (simplified):
Date|Customer|Actual Amount|Total Monthly Projected Amount
I want to show, at the month level, the total actual sales, the projected amount for each month, and the difference between the two. I also want to show the Grand Total (which acts as the YTD figures also).
I put the measure names in the columns shelf, the month/date in the rows shelf, and the Measure Values in the text shelf. The total actual sales amount per month shows up correctly as SUM(Sales). For the projected amount, I need to change this to be AVG(Projected). For the difference, I created a calculated field: Sum([Sales]) - Avg([Projected]).
However, in my Grand Totals, the grand total for Avg(Projected) is simply the average of all of those values in that column. I want to show the sum of those (average) values.
(I have also include a running sum columns, for sales, projections and differences, but having the Grand Total makes this all easier to see. The last row with the running total shows the accurate grand totals.)
Finally, I have added a calculated field for commissions based on the difference between sales and projections. If sales > projections by 10%, then commissions are calculated on that amount. If sales<projections by 10%, then a negative commission is applied. Including this calculated field in the workbook works fine for each row of data. But in the Grand Total, the grand total for this field should be based on the total actual sales vs the total projections. Instead, I get some value I don't fully understand. How can I base this grand total on the total sales compared to total projections?