Pivot table formatting

[gwoolley0302]

Hello,

I've tried to source a solution to this but have struggled so far, so wondering if someone would be able to help.

I have a pivot table like the one below. I have chosen to summarise the values as'the % of row total', which is really helpful. However, to help set context of the data, I would also really like to show the total 'number of data points' per row. (I.e. for cell B3 below - I want to help the reader of this know what 3% is a total of?).

So ideally would a total column at the end (as presented in the final column) which shows the number rather than a %. From what I can see, there only seems to be the option of matching the data format across the whole pivot table.

Or is there an option to have a pivot table, which summarises the data in both a % and a number for every cell?

(P.s. I am using Microsoft 2018) Really appreciate any help.

Best wishes, Greg

Count of ID	Column Labels					Ideally I would like to show...
Row Labels	1. Below	2. Working towards	3. Just within	4. Within	5. Above	Total number of individuals counted
1. Below	3%​	24%​	73%​	0%​	0%​	37​
2. Working towards	0%​	4%​	4%​	93%​	0%​	256​
3. Just within	0%​	0%​	0%​	0%​	100%​	7​

 

[Roger Govier]

Hi
Can you show a sample of what your source data looks like?

 

[gwoolley0302]

Hi Roger,

Please see the link below:

L2L2R Impact system 2019.12.16a.xlsx

drive.google.com

Unfortunately it is quite a large file. It uses a Power Pivot Query, so various tables from tabs 3 - 6.

The example table in my original post is from row 108 in tab 7.

Does that help?

Thanks, Greg

 

[gwoolley0302]

Does anyone have any thoughts on this?

 

[sandy666]

maybe turn on Grand Total for Rows only

 

[Roger Govier]

@gwoolley0302
Sorry, after you posted your file I had to go out to a clients site.
I have just taken a look at your file, and your data is not in the best way for Pivoting. It is not in a first normal format.
Nevertheless, you have managed to produce a report in the way that you want.
I cannot see any way of combining your absolute values of a count within the pivot table. The suggestion of adding Grand Totals for rows will not work, as that will merely tell you 10o% for each row.

If you copy your Pivot table and paste it further across the sheet from your existing table, I chose column O, and in this copy set the Field to be Count with No Calculation, you will have the numbers. Turn on Grand Total for rows in this copy.
Then alongside your first table, in column H you could enter in H108 =IF(B108<>"",U108,"") and copy down.
The columns containing the second PT could then be hidden if you wish.

This is a fudge, and your Totals are not part of your original PT, but for reporting purpose it would achieve what you want.