Eric--

The following should solve the issue. I first created the Average calculation which allows me to find the annual average. Second, I created the 50% of Previous Average calculation which allows me to grab and half the previous year's average. This has to be done in separate calculations as the two calculations require different compute using settings.

Hope this helps,

Dan

Consider it done

Dan

Fair enough. I will list the steps for the entire view here:

1. Create the Average calculation
2. Add it to the view and set it to Pane Across -- this gives us our annual average
3. Create the 50% of Average calculation
4. Add it to the view, right click the pill, and hit edit table calculation
5. From here, you will notice that there is a dropdown in the edit dialog. This is due to the fact that the 50% Average calculation relies on another table calculation. Because of this, we have to use the edit dialog here to set the proper compute using.
6. Ensuring that the 50% Average calc is shown on the dropdown, hit compute using, select advanced, and mimic what I have in the attached workbook.
7. Setting this at the level of years determines the level of granularity which is applied to our -1 offset in lookup. In other words, we will look up the previous year's average in each month of the current year.
8. Create the color calculation
9. Since all the underlying table calculations have already been set, you do not need to set anything up for this field. Click the Sum Hours Spent pill and place the color calculation on color for this field.
10. You notice we get three colors to which you can assign values (double click the legend and assign them). True and False come from the fact that the color calculation is a boolean statement. The Null is the result of there not being any values for the 50% Average field in the first year in the view.
11. Finally, change the color of the 50% Average bar to something else.

I have also added a different version of the workbook that uses a reference line to show the average. It is slightly cleaner as it 1) draws the line across the whole pane and 2) allows you to dual axis in a different measure if needed.

All I did to accomplish this was drag the 50% Average calc to the Detail shelf, right click the hours spent axis, and add a reference line. You can see my settings there.

Hope this helps,

Dan

Always glad to help.

Do not worry about posting again. This is a fairly difficult table calculation.

Here is a list of the problems and how you can recognize them. I am able to recognize them without looking at the calcs as I have a lot of experience troubleshooting things like this. As you play with these more and more, you will start recognizing these symptoms and their solutions.

• Only the first bar is colored blue for Null. This tells me that the lookup is running at the level of month and not year. There are two causes to this: 1) you did not set the at the level to Year or 2) the order of the fields in compute using is incorrect
  • In this case, it is the fact that the order of the compute using is incorrect. You have month above year in the 50% previous addressing in the compute using settings. This must be reverse. In general, the order of this can be thought of in the following way: left to right equals up to down. In other words, year should be above month in addressing because year is the leftmost pill on columns. The order here is crucial.
  • Changing this on both the 50% of Previous and ColorChooser fields will fix the issue.
• The reference line is being drawn in both panes. Furthermore it is the same number in each pane. This tells me that the calculation driving the average is being run table across rather than pane across. Setting it to pane across would result in two different averages unless the data is exactly the same in the panes
  • For both the ColorChooser and 50% of Previous pills you need to right click, click Edit Table Calculation, change the dropdown to Average, and change table across to Pane Across. This will give us an average for each year rather than an average of both years combined.

If some of this vocabulary is confusing, you can always watch my quick video about table calculations. I try to address a lot of the vocabulary that I have been using throughout this thread. You can find it in the Advanced section of our training videos. It is called Table Calculations.

Hope this helped,

Dan

Post edited above. Thanks for your patience.

Dan

Dan,

Is there a way to keep these calculations and remove 2012 from the display? Thanks

Absolutely. Make a calculation that is the following formula: index()!=1

This reads as index is not equal to one. In other words, we will be able to hide everything after the first year.

Add this calc onto the level of detail, right click it, click edit table calculation, and hit advanced on the compute using dropdown. Once open, move Year of Month/Year to Addressing while leaving Month of Month/Year in Partitioning. This will tell our index to run along our years such that FY 2012 gets an index of 1 and FY 2013 gets an index of 2.

Then all you need to do is drop this cal on the filters shelf and keep the True value.

I hope this helps,

Dan