I have a table which consists of the following:
Date | Region |Revenue Type| Revenue Amount |
There are 20 Revenue Types (A,B,C....Goal), with the last type being a goal for A, B, and C.
I'd like to be able to graph the sum of A, B, C against the Goal. The Goal would be on a Dual Axis.
Is there a way to create a calculated field for SUM(A,B,C) and contrast against the Goal in a single line graph?
The easiest way I could come up with is by creating one calculated field Goal with [Revenue Type] = 'Goal'. This will give you a boolean value that inidcates if it is the goal or another type.
If you drop [date] on columns and [Revenue Amount] on rows, you get one timeline. If you then drop [goal] on colour, you will get 2 different timelines, one for your goal and one for the sum of all the others.
If you want to have the goal as a different chart (eg. a line in front of bars), you could create 2 calculated fields: IF [Revenue Type] = 'Goal THEN [Revenue Amount] END and IF [Revenue Type] != 'Goal THEN [Revenue Amount] END
This will give you 2 measures, one for the goal, one for all the others and based on these you could create a dual axis chart.
