19 Replies Latest reply: Dec 2, 2016 12:34 PM by John Jackson

# How to do this in Pivot Table

I have a dimension less pivot table and I want to get rid of the row as show below. is it possible. pls see qvw and excel file attached.

Many Thanks

• ###### Re: How to do this in Pivot Table

How about using a straight table?

• ###### Re: How to do this in Pivot Table

Hi Sunny, how were you able to list expressions at row level? when you add expns in a straight table, they get added to the right. that is why I chose pivot table. can you pls explain. Thanks

• ###### Re: How to do this in Pivot Table

On the Presentation tab of straight table, you will find the option to check 'Horizontal', this will change the view of your chart to how you see in the image above.

Best,

Sunny

• ###### Re: How to do this in Pivot Table

Thank you Sunny!!

• ###### Re: How to do this in Pivot Table

Hi Sunny, is it possible to have only 'Total Liability' (Expression Label) in red font? the expression itself can be done but can the label be done as well?

• ###### Re: How to do this in Pivot Table

May be using an alternative technique to do this then

• ###### Re: How to do this in Pivot Table

That was awesome. additionally, I needed metrics number to be red and for that I did as in the image.

so from the expression you used,

Pick(Dim1,

\$(V_assets),

Num(\$(V_Equity_per), '##.00%'),

\$(V_Liability),

\$(V_Stock),

\$(V_Expenses))

Liability gets the value Dim1 =3....? can you pls explain how this logic works. I know I am asking for a lot.

• ###### Re: How to do this in Pivot Table

Hi Sunny is it possible to flip the positions of the colmns. so can I have numbers in 1st coumn and text in 2nd?

• ###### Re: How to do this in Pivot Table

You sure can. Add a new expression Dim with the same settings that we applied to the actual dimension.

One the presentation tab, hide the actual dimension

• ###### Re: How to do this in Pivot Table

Thank you for your reply. trying to understand the logic... so to be able to flip the column in straight table, it has to be in Expression form?

• ###### Re: How to do this in Pivot Table

It is just the way it is structured that we need to create a new expression to get this done. But is this not what you wanted?

• ###### Re: How to do this in Pivot Table

yes this is what I want. Thank you.

• ###### Re: How to do this in Pivot Table

stalwar1

Hi Sunny I am struggling with a related problem to this thread.

I need to have conditional background-color formatting in 1st two expns:

1) For Expn Total Company Assets, I need the background color of numerical value to be Green RGB(74,159,62) if > 80

or else red RGB(255,0,0). so in this case 83 will have a green background

2) For Gain Percentage, I need the background color of numerical value to be Green RGB(74,159,62) if > 50%

or else red RGB(255,0,0). so in this case 39.76% will have a red background.

I tried playing with pick same logic you applied to calculate main expn but no luck. Thank you in advance

• ###### Re: How to do this in Pivot Table

Would you be able to share a sample?

• ###### Re: How to do this in Pivot Table

Or is the same sample which is attached is applicable here?

• ###### Re: How to do this in Pivot Table

Sunny,

its the same sample attached above.

• ###### Re: How to do this in Pivot Table

May be like this:

=If(Dim1 = 1, If(\$(V_assets) > 80, RGB(74,159,62), RGB(255,0,0)),

If(Dim1 = 2, If(\$(V_Equity_per) > 0.50, RGB(74,159,62), RGB(255,0,0))))

• ###### Re: How to do this in Pivot Table

Thankyou for quick reply. I will apply in my actual app and update.

• ###### Re: How to do this in Pivot Table

Sunny,

Thank you again. That was it. You killed it!