# % of column and % of grand total?

1. ## % of column and % of grand total?

Hi,

I've attached a sheet and just realised the figures aren't quite right. I would like the boy and girl column to read percentage of boys that got the question correct and percentage of girls that got the question correct. However the grand total should be percentage of children overall that got the question correct if that makes sense?

When I have it as % of grand total the table reads correctly but the graph is way off...

3. ## Re: % of column and % of grand total?

Oh sorry. It was too big so I've zipped it.

4. ## Re: % of column and % of grand total?

In what way is the graph way off? It's plotting the data from the table. Are you thinking it can somehow stack 33% and 40% and get a total of 38% (for not attempted)?

5. ## Re: % of column and % of grand total?

Yeah, I suppose I want the graph to plot the grand total column and show what division that is between boys and girls. Is that possible?

6. ## Re: % of column and % of grand total?

If your pivot table layout is fairly static, you could use a helper table alongside and plot that chart from that?

7. ## Re: % of column and % of grand total?

A helper table? Ok, never heard of them, I'll Google it and see what comes up!

8. ## Re: % of column and % of grand total?

It's just a table of formulas alongside your pivot table to calculate the correct boy/girl percentages of the grand totals and then plot those as a chart.

9. ## Re: % of column and % of grand total?

Ok, thanks. I think a PivotTable may be the wrong tool for the job - I'll try to recreate this analysis with formulas...

10. ## Re: % of column and % of grand total?

is that what you want?

but PowerQuery is required

11. ## Re: % of column and % of grand total?

Am I missing an attached Sandy?

12. ## Re: % of column and % of grand total?

There is an attachment - a .zip file.

13. ## Re: % of column and % of grand total?

Originally Posted by CraigMcKee
Am I missing an attached Sandy?
No, you don't - just refresh thread

14. ## Re: % of column and % of grand total?

Thank you - I could read your reply but couldn't see the attachment for some reason Got it now, I'll have a look. Thanks.

15. ## Re: % of column and % of grand total?

I don't know why but forum doesn't allow to upload xlsb file but it should (less than 10 MB) so maybe you check post before second attempt with a zip

16. ## Re: % of column and % of grand total?

Hi Sandy, no it's not. I want the table to read percentage of boys and girls that got it correct but as a percentage of boys and girls. I would like the chart to show overall percentage of children that got it correct with a boy girl breakdown if that makes sense.

17. ## Re: % of column and % of grand total?

I grew up not far away from English Martyrs'. This is a public forum, and I am not sure they would like to see their data published here. You really ought to remove it and provide dummy, desensitised data instead, what with data protection, GDRP and all that.

18. ## Re: % of column and % of grand total?

like this or I misunderstood

school.jpg
'
Clipboard01.jpg

maybe show the result what you want to achieve. Do that manually if necessary

attach source tab and result tab only. You don't need to attach whole file

19. Ali it is made up data. Nothing in there that is true at all. Just picked some random school names to test my data. Sandy, I'll send something back as soon as I'm back at my laptop - thank you.

20. ## Re: % of column and % of grand total?

OK, well consider that those 'random' schools might not be very happy!!!

Just use School A, School B, etc.

21. ## Re: % of column and % of grand total?

I've attached a screen shot of what I'm trying to achieve.

https://snag.gy/qh8s6v.jpg

The table shows the data relating to boy girl then I've created columns g - i which show the scores as a % of the overall figure - otherwise if all boys and all girls were correct then this could potentially read 200%!

22. ## Re: % of column and % of grand total?

That is how I would do it. Unless your tables are going to change layout a lot, it's as easy a way as any, I think.

23. ## Re: % of column and % of grand total?

I've done it this way now creating a new PivotTable that I'll hide.

I'm going to have a different sheet for every question so it is possible to copy this sheet with all the PivotTables and just change them to question 2 rather than question 1 without having to go thorugh all the calculations and formatting options for each one?

24. ## Re: % of column and % of grand total?

Yes. You could also just have one pivot table for all the questions and then link each table as required using GETPIVOTDATA formulas.

25. ## Re: % of column and % of grand total?

Hm, I feel a little
How you get Boy/Girl/Incorrect = 0 ?
Could you show the process of your calculation?

26. ## Re: % of column and % of grand total?

Sandy - no children got that one incorrect. GetPivotData - thank you, I'll take a look. I sometimes wish I'd never delved into the murky depths of PivotTables !

27. ## Re: % of column and % of grand total?

from your source table it appears that Incorrect for: Boy = 2, Girl = 1
Nevermind, if you found a solution

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1