Referencing a variable chart label within the chart

[#1]

Hello,

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?

Thanks.

[Miguel_Angel_Baeyens]

Hello,

I'm not sure if I got you right but you can do something similar to label

='% Sales' & vYear

and refer it within an expression

[% Sales $(vYear)] / 100

Now twist this code and change with your own variables. Is that what you are looking for?

[#3 · Accepted solution]

As well as referencing a column name, you can also reference a column number, specifically because of this case.

Your first expression is column 1, then 2, then 3 etc.

So, if you have Sales as your first column, margin as you second, then your expression for GM% should be Column(2)/Column(1)

[#4]

That's it exactly! I figured there had to be a simple way to do it. Thanks.