Pivot Table - Show as Percent of Column Total greyed out


I have two queries in the attached: Works, Notworks
I have designed them as a pivot table.

The "Show as Percent of Column Total" seems to be greyed out in the "Notworks" query and it is not greyed out in the "Works" query.  I dont know why because I have taken the SQL and copied and pasted from "Works" to "Notworks" so they are identical

I have compacted and repaired and decompiled.

I imagine it is something simple but I have spent too much time on this.    

Please see attached 2010 db and open the queries and show as Pivot and select the field [Percent] and try to "Show as Percent of Column Total" which is found in the Pivot Table Tools / Tools group on the far right.  It is greyed out only in "NotWorks".

thank you
pdvsaProject financeAsked:
Who is Participating?
hnasrConnect With a Mentor Commented:
@ n the Pivot Table Tools / Tools group on the far right.  It is greyed out only in "NotWorks".
You need subtotals to enable the Show As Button, and not detail entries.

To enable Show as button.
Select Type heading, Select Total > Subtotal
Select Percent heading, Select Show/Hide > Hide Details
Select a value in Percent column, Tools > Show As > Percent of Grand Total

You may add subtotals for other fields.
Jeffrey CoachmanMIS LiasonCommented:
I am not sure,
Note that the WORKS pivot has actual "Percents" displayed in the Percent column, and also has a Grand total,
...The NotWorks query only has "values" in the percent column and no grand totals.
I have not looked a Pivots in Access since they were deprecated,
...So if you find the reason for this "Percents vs Values" discrepancy, you will have solved the mystery here.
(and note that since they were deprecated, you will eventually have to find another way to do this...)

...but also note that your pivot table is based on a complex "Group by" query.
A group by query, by design, is a "summary" query and will not show individual values
...so it may not know what you mean by "total" (total of a total?)
So it seems like you are getting a summary, then trying to use a Pivot to get another summary of your summaries?

Why not just try making the Pivot from the raw data alone.
Or calculate the percentage in a simple query,...then do a pivot of that simple query, to include the percent.

But let's see if another expert, who has more knowledge of Pivots, chimes in...

Show an example of the working PivotTable. Limit the fields, in the source queries, to only include the relevant fields.

Explain the difference between the two queries. Works, and NotWorks.
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

The SQL statements are identical, but it looks like, that Access is storing in the sys-tables further information on Pivot-View.
Interestingly the design view graphic of the 2 queries looks different too.
In my Access 2010 i did following tests: copy both queries >> look the same as originals. The not working lacks the data field, but you can drag from the right field selection box the Percent field. Then it is correct and if you save it, this correct view comes again. So this shows, that Access keeps some not documented properties somewhere, which make a difference. Maybe you tested someting with the NotWorking Query or you didd something slightly different in design view, which is not reflected by the SQL, but is kept in some Access query properties.
pdvsaProject financeAuthor Commented:
bonjour-aut, thank you for the response.  I however can not see what you did to fix my issue.  Can you let me know?

I do not follow this part of your solution:  "The not working lacks the data field, but you can drag from the right field selection box the Percent field."  ===>I do see the [Percent] field and I have dragged it both out and in the pivot and the "Show as Percent of Column Total" remains greyed out.  

thank you for the help.
pdvsaProject financeAuthor Commented:
I have also tried to copy and paste the SQL of the query you fixed into my db but the Show as Percent of column total is still greyed out.
pdvsaProject financeAuthor Commented:
I also tried to recreate the query to remove any possible hidden properties and still the "Show as Percent of column total is still greyed out"  for the [Percent] field
Please see my 2 screenshots: 'NotWorks1.jpg' shows original state, where i drag and drop the Percent field. 'NotWorks2.jpg' shows the result as also seen in the db under NotWorks_copy query.
pdvsaProject financeAuthor Commented:
hello....thank you for the pics.  I still do not see enough with the pics.  I do not have an issue with dragging the field [Percent]m to the pivot.  The issue is enabling the greyed out button.   Please see the image below.  I also see you are in pivot chart view but I am in pivot table view.  I do see that in your query in the previously attached db that the button is enabled and I need to know how you did that.   I do not believe simply dragging [Percent] over is the solution because I have done this many times.  Please let me know what is next.  

pdvsaProject financeAuthor Commented:
Thank you.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.