7 Replies Latest reply on Nov 21, 2018 2:04 PM by Sushmitha Reddy

# Adding a column at the end of a tabular worksheet having dual axis measures

I have a worksheet that looks as follows: As you can see on the Columns, there are couple of measures (which use dual axis for color conditional formatting i.e., like W2_1, W2_0 are dual axis measures; W3_1, W3_0 are dual axis and so on). Now I want to put another measure (WoW) such that it appears as the last column in the table. How do I do that? If I put it on the Rows, the column appears after the GOAL dimension in the table (i.e., comes as the 4th column in the talble). If I put it on the Columns, the whole table is screwed up like this: How do I get this done??

Updated screenshot based on the below comment: Now I have everything in Measure Values. But now how do I have the conditional color formatting for each measure? I have a calculcated field for each measure i.e., W1, W2, W3 and so on... the calculcated fields are like this for each measure:

IF [GoalSign] = '='

THEN

IF [M1] = [GoalCalc]

THEN

"True"

ELSE

"False"

END

ELSEIF [GoalSign] = '>='

THEN

IF [M1] >= [GoalCalc]

THEN

"True"

ELSE

"False"

END

ELSEIF [GoalSign] = '<'

THEN

IF [M1] < [GoalCalc]

THEN

"True"

ELSE

"False"

END

ELSEIF [GoalSign] = '>'

THEN

IF [M1] > [GoalCalc]

THEN

"True"

ELSE

"False"

END

ELSEIF [GoalSign] = '<='

THEN

IF [M1] <= [GoalCalc]

THEN

"True"

ELSE

"False"

END

END

• ###### 1. Re: Adding a column at the end of a tabular worksheet having dual axis measures

Can you provide your packaged workbook with (sample) data?

• ###### 2. Re: Adding a column at the end of a tabular worksheet having dual axis measures

Hi Anjali,

Since you're just building a table, and don't need separate mark types for each measure, I'd highly recommend that instead of placing each individual measure on the columns shelf, that you place the "Measure Names" field on the columns shelf, and "Measure Values" on your Text mark instead.  That will allow you to put as many measures as you want on your view.

Let me know if you have any questions.

Best,

Paul

• ###### 3. Re: Adding a column at the end of a tabular worksheet having dual axis measures

Hi Paul,

Thanks for your comment. I have provided the screenshot of my worksheet after doing as you suggested. But now I have problem with conditional color formatting. I've provided the details for that above in the question. Can you please have a look and see if you can help me.

• ###### 4. Re: Adding a column at the end of a tabular worksheet having dual axis measures

Anjali, what you were doing earlier was right because you had that special conditional color formatting. As far as adding the new column is considered, why don't you go ahead and add a pair of columns as before at the end? If you are unable to place it at the end, you may first want to place it in between, where possible, and then move these columns to the end, or drag the other columns forward. If you are still not able to do it, I will need to see your workbook, and that's why am asking for it.

• ###### 5. Re: Adding a column at the end of a tabular worksheet having dual axis measures

Hi Anjali,

What does your conditional color formatting do?  Does each measure have different possible color values?  If so, then you will need to have each separate on your color column.

However, if you would simply like the ability to color each measure separately, you can do that from the measure values pill.  On your Measure Values pill on your color shelf, right click and select "Use Separate Legends".  You can then color each measure by their individual values and set separate colors.

Best,

Paul

• ###### 6. Re: Adding a column at the end of a tabular worksheet having dual axis measures

While you are exploring the solution I suggested above, is there any way that you can pivot your data so that all your measure names are in a single column, and the measure values in another column? If yes, then you don't even need to do the dual-axis work as mentioned. It will be much easier to build the sheet and have the required coloring logic.