4 Replies Latest reply on Jun 27, 2016 8:11 PM by Nicholas Gorman

# Help with Formatting

Hi Everyone,

I need some help with coloring some fields in a very particular way.

The goal is to have each row be the sum of people who responded either positively or negatively to a certain question. (Image attached with stacked bar graph look.)

The data is layed out so that each column is a particular question, and each row is a customer. (Mock data attached)

 Customer Q1 Q2 Q3 Q4 cust-1 2 0 1 2 cust-2 1 2 2 2 cust-3 1 2 1 2 cust-4 2 0 1 0 cust-5 1 1 1 1 cust-6 2 0 2 1 cust-7 1 1 2 0 cust-8 2 0 1 1 cust-9 1 2 0 0 cust-10 0 1 0 0 cust-11 1 0 1 0 cust-12 0 2 2 1 cust-13 0 2 0 1 cust-14 2 2 2 2 cust-15 0 2 2 0 cust-16 2 0 2 0 cust-17 1 2 0 0 cust-18 1 2 0 1 cust-19 2 2 0 0 cust-20 0 0 0 0

If a one is next to the customer, they said that they had a positive experience with that aspect of the business. If there is a 2 they they said that they had a negative experience with that aspect of the business. A zero means they did not have an opinion about that aspect.

I have managed to align the data so that I can color this for one row at a time, but cannot for all of the rows on the same graph.

I cannot do individual sheets and put them all into a dashboard.

Any help would be appreciated!

Thanks,

Nick

• ###### 1. Re: Help with Formatting

Nick,

I think it will help to pivot your data using the method described here:

Pivot Data (from Columns to Rows)

Probably more complicated than necessary, but I used a Level of Detail calculation to get the percentage:

{ FIXED [Question],[Response]:COUNT([Response])}/{ FIXED [Question]:COUNT([Response])}

I put [Percentage] on Columns, [Question] on Rows, and [Response] on Color.

On the Color legend, I right clicked on "0" and selected "Hide".

You may want to avoid using the colors Red and Green together.

1 of 1 people found this helpful
• ###### 2. Re: Help with Formatting

Thank you for all the help.

That being said, I am having a small problem with the supposed solution.

When I filter the data by quarter the percentages do not change,

Although they should. I have attached a similar mock workbook with a similar problem.

I cannot simply. attached is a document displaying a similar problem.

Notice in the first sheet "Unfiltered" how the sums matchup perfectly with the percentages.

but in the second sheet, when the quarter filter is tacked on, they no longer match up correctly:

• ###### 3. Re: Help with Formatting

Nicholas,

Sorry for not taking that into account.

The Level of Detail calculation using "Fixed" will make its calculations

only with respect to whatever is in the first part of the declaration:

{ FIXED [Pivot field names],[Pivot field values]:COUNT...

And so, you will need to add Quarter to both lists:

{ FIXED [Pivot field names],[Pivot field values],[Quarter]:COUNT([Pivot field values])}/{ FIXED [Pivot field names],[Quarter]:COUNT([Pivot field values])}

1 of 1 people found this helpful
• ###### 4. Re: Help with Formatting

Thank you for the continued help. This did solve the problem.

Thanks!