Pivot table question: %satisfaction

Question

Hi, 

I have a column called "Satisfaction", where 0 is for a bad rating, 1 for a good rating, and empty for a non-rated support ticket.

I wanted to create a pivot table (check images below), with the following columns:

• Rated

• % Rated
• % Satisfaction

But not getting the results that I wanted. The % satisfaction is ok but I have the DIV/0 situation to solve.

The #Rated, not getting is with count or sum, because count, counts all rows, and sum only good ratings.

Any thoughts on this?

Answer 1

Dear Pedro,

Thanks for your updates.

If the value is number, the calculated column will be showed as Sum which isn't Count by default. So it will be hard to display % Rated (Count of Rated / All Rated (Count of ticket month) ) in Pivot Table in Excel.

As a workaround, for example, I type good and bad instead of 1 and 0 in the satisfaction column and then add a new column Count of Rated for Pivot Table top count the rated amount by Sum. No matter good or bad, it will be counted as 1 rate. You can type =IF(OR(B2="good",B2="bad"),1,0) to calculate the column values in the original sheet. 

Then add the calculated column Sum of %Rated in Pivot Table with the formula ='Count of Rated '/7 and then format the cells as % to see the outcome.

Best Regards,

Cliff

Answer 2

Thank you Cliff.

It's not quite that. That way, the "% Rated" column retrieves the % of dis distribution of the "# Rated" column.

What I want to get is the % rated of all rows (in this particular case "support tickets").

I have 49 tickets, 4 of those tickets were rated, 3 for support, 1 for Helpdesk .

The rated ticket for "helpdesk" is Bad (0), and for Support 2 were good (1) and one bad (0)

% satisfaction for Helpdesk is 0%, 1 rated ticket, and the % rated should be 2% (1 / 49)

For support, the % satisfaction is 66,67%, and the %rated ticket should be 6,12% ( 3 / 49) 

Can't find a way to get the %rated

I added the "count of id" (total ticket count) for better perception, but if it can help to calculate the %rated I can leave it in the pivot table

Answer 3

Dear Pedro,

Welcome to the forum here.

Generally, you can just change the #Rated filed settings by clicking on Field Settings>Count>Show Values As>% of Grant Total to see the result.

If something is misunderstood, welcome to share any updates when you have time.

Best Regards,

Cliff

Answer 4

To resume, for % Rated, I would like the % of rated tickets, without having to add a column for the total count ..

Answer 5

Ok found it for #Rated, its "count numbers"