3 Replies Latest reply: Apr 6, 2010 2:03 PM by John Lewis RSS

    Referencing a variable chart label within the chart


      I have a chart that shows typical sales information: Sales, Margin and GM%. GM% is calculated as Margin / Sales. The Sales and Margin data can be for any year the user selects. They also have a series of If statements in their expression looking at other selections the user can make in the document. What I want to do is make the Sales and Margin column heading dynamic, showing the year selected, but also be able to reference the expression label to make the GM% definition relatively easy. For example, my Sales and Margin labels would look something like this:

      =(SelectYear) & ' Sales'

      =(SelectYear) & ' Margin'

      But how do I reference these expressions in my GM% definition calculation? Before I added the year to the column heading I had:

      Sales had the label = Sales

      Margin had the label = Margin

      And GM% had the definition = [Margin] / [Sales]

      I don't want to have to repeat the definitions for Sales and Margin again in the GM% definition if I don't have to because they can be a little involved. Is there a way to have the column label be one value and the actual data another value to make referencing it within the chart easy?