Separate Reference line for each Dimension value

[smiling_cheetah]

Hi everyone,

I have a bar chart with "Month A" as a dimension and "Sales A" as a measure.

What I want to do is to add 12 reference lines (for each "Month A" value, with a measure like SUM({<MonthB = {'Jan'}>} "Sales B")

Basically, the question is: Can I add a reference line for ONE dimension value?

Is it even possible, and if so, how I can do it?

Would appreciate any help,

Thanks in advance

[DavidŠtorek]

Hi,

as it is obvious from its name the reference line is a LINE. So basicaly it can have only one value for each dimension values.

What you want to do is possible with usage of combo chart. Just create table with target values and use it as second measure.

Hope it helps

[OmarBenSalem]

Use a combo chart; and add this new (reference Line) as a new measure with a different shape of ur first measure

[smiling_cheetah]

Thanks, that could've worked,

but the deal is that I have, say, two separate tables:

MonthA	Sales A
Jan	1000
Feb	1100

MonthB	Sales B
Jan	500
Feb	700

I want to have a chart with one dimension and two measures.

First part is easy:

I put MonthA as a Dimension and sum(Sales A) as an expression.

Second part is hard:

Expression is sum(Sales B), but I want the expression to perceive Dimension as not what it is(MonthA), but as MonthB.

(Because it will not perform the calculation, as MonthA and Sales B are simply not connected in the model)

[smiling_cheetah]

Thanks Omar for your suggestion,

but the deal is I have two not connected tables (I described the scenario in a reply above), and this solution unfortunatelly will not work

[DavidŠtorek]

That has simple solution...connect those two tables (what is preventing you from doing this?)

[smiling_cheetah]

Heh, I doubt if it will solve this problem

In reality the tables are, in fact, connected and look like that:

Phase1ID	Phase1Month	Phase1Value
1	Jan	1000
2	Feb	1100

Phase2ID	Phase1ID	Phase2Month	Phase2Value
30	1	Feb	500
31	2	Mar	700

And as a result I want a chart which looks like that:

Month	Phase1ValueSum	Phase2ValueSum
Jan	1000
Feb	1100	500
Mar		700

[DavidŠtorek]

Do you want to set targets by month names? I mean for Jan in Phase1Month you want value from Jan Phase2Month?

If yes,than I suggest to create new table (resident from the second). Use it like this

Left join(<first table>)

Load

Phase2Month as as Phase1Month,

Phase2Value as Target

Resident <second table>;

And it will work