Pivot Grand Total for Power Pivot not displaying correct

A

Ackermhc

New Member
Joined
Nov 28, 2017
Messages
2
Hi<o:p></o:p>


I have the following pivot table created from apower pivot table, linked to SQL data base. When I select the Grand Total (68) it does not display the 68 recordsbut instead opens the whole table related to the pivot table source. It wasworking fine before. <o:p></o:p>


[TABLE="class: MsoNormalTable, width: 722"]
<tbody>[TR]
[TD="width: 207"] ASC - Job State Age Period<o:p></o:p>
[/TD]
[TD="width: 107, bgcolor: transparent"][/TD]
[TD="width: 67, bgcolor: transparent"][/TD]
[TD="width: 135, bgcolor: transparent"][/TD]
[TD="width: 117, bgcolor: transparent"][/TD]
[TD="width: 80, bgcolor: transparent"][/TD]
[TD="width: 71, bgcolor: transparent"][/TD]
[TD="width: 81, bgcolor: transparent"][/TD]
[TD="width: 99, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 207"] AOI Claimed<o:p></o:p>
[/TD]
[TD="width: 107"] 1<o:p></o:p>
[/TD]
[TD="width: 67, bgcolor: transparent"][/TD]
[TD="width: 135, bgcolor: transparent"][/TD]
[TD="width: 117, bgcolor: transparent"][/TD]
[TD="width: 80, bgcolor: transparent"][/TD]
[TD="width: 71, bgcolor: transparent"][/TD]
[TD="width: 81, bgcolor: transparent"][/TD]
[TD="width: 99, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 207"] task_Org<o:p></o:p>
[/TD]
[TD="width: 107"] (Multiple Items)<o:p></o:p>
[/TD]
[TD="width: 67, bgcolor: transparent"][/TD]
[TD="width: 135, bgcolor: transparent"][/TD]
[TD="width: 117, bgcolor: transparent"][/TD]
[TD="width: 80, bgcolor: transparent"][/TD]
[TD="width: 71, bgcolor: transparent"][/TD]
[TD="width: 81, bgcolor: transparent"][/TD]
[TD="width: 99, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 207, bgcolor: transparent"][/TD]
[TD="width: 107, bgcolor: transparent"][/TD]
[TD="width: 67, bgcolor: transparent"][/TD]
[TD="width: 135, bgcolor: transparent"][/TD]
[TD="width: 117, bgcolor: transparent"][/TD]
[TD="width: 80, bgcolor: transparent"][/TD]
[TD="width: 71, bgcolor: transparent"][/TD]
[TD="width: 81, bgcolor: transparent"][/TD]
[TD="width: 99, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 207"] Count of Reference_Order_No<o:p></o:p>
[/TD]
[TD="width: 107"] Job_State<o:p></o:p>
[/TD]
[TD="width: 67"][/TD]
[TD="width: 135"][/TD]
[TD="width: 117"][/TD]
[TD="width: 80"][/TD]
[TD="width: 71"][/TD]
[TD="width: 81"][/TD]
[TD="width: 99"][/TD]
[/TR]
[TR]
[TD="width: 207"] JS_Period<o:p></o:p>
[/TD]
[TD="width: 107"]
RFA<o:p></o:p>​
[/TD]
[TD="width: 67"]
EXE<o:p></o:p>​
[/TD]
[TD="width: 135"]
CDR<o:p></o:p>​
[/TD]
[TD="width: 117"]
CNR<o:p></o:p>​
[/TD]
[TD="width: 80"]
TND<o:p></o:p>​
[/TD]
[TD="width: 71"]
CANRE<o:p></o:p>​
[/TD]
[TD="width: 81"]
CAC<o:p></o:p>​
[/TD]
[TD="width: 99"]
Grand Total<o:p></o:p>​
[/TD]
[/TR]
[TR]
[TD="width: 207, bgcolor: transparent"] 0-10<o:p></o:p>
[/TD]
[TD="width: 107, bgcolor: transparent"]
34<o:p></o:p>​
[/TD]
[TD="width: 67, bgcolor: transparent"]
7<o:p></o:p>​
[/TD]
[TD="width: 135, bgcolor: transparent"][/TD]
[TD="width: 117, bgcolor: transparent"]
3<o:p></o:p>​
[/TD]
[TD="width: 80, bgcolor: transparent"]
1<o:p></o:p>​
[/TD]
[TD="width: 71, bgcolor: transparent"]
1<o:p></o:p>​
[/TD]
[TD="width: 81, bgcolor: transparent"]
1<o:p></o:p>​
[/TD]
[TD="width: 99, bgcolor: transparent"]
47<o:p></o:p>​
[/TD]
[/TR]
[TR]
[TD="width: 207, bgcolor: transparent"] 61-90<o:p></o:p>
[/TD]
[TD="width: 107, bgcolor: transparent"][/TD]
[TD="width: 67, bgcolor: transparent"][/TD]
[TD="width: 135, bgcolor: transparent"][/TD]
[TD="width: 117, bgcolor: transparent"]
1<o:p></o:p>​
[/TD]
[TD="width: 80, bgcolor: transparent"][/TD]
[TD="width: 71, bgcolor: transparent"][/TD]
[TD="width: 81, bgcolor: transparent"][/TD]
[TD="width: 99, bgcolor: transparent"]
1<o:p></o:p>​
[/TD]
[/TR]
[TR]
[TD="width: 207, bgcolor: transparent"] 11-20<o:p></o:p>
[/TD]
[TD="width: 107, bgcolor: transparent"]
1<o:p></o:p>​
[/TD]
[TD="width: 67, bgcolor: transparent"]
1<o:p></o:p>​
[/TD]
[TD="width: 135, bgcolor: transparent"][/TD]
[TD="width: 117, bgcolor: transparent"]
5<o:p></o:p>​
[/TD]
[TD="width: 80, bgcolor: transparent"][/TD]
[TD="width: 71, bgcolor: transparent"][/TD]
[TD="width: 81, bgcolor: transparent"]
1<o:p></o:p>​
[/TD]
[TD="width: 99, bgcolor: transparent"]
8<o:p></o:p>​
[/TD]
[/TR]
[TR]
[TD="width: 207, bgcolor: transparent"] 21-30<o:p></o:p>
[/TD]
[TD="width: 107, bgcolor: transparent"][/TD]
[TD="width: 67, bgcolor: transparent"]
1<o:p></o:p>​
[/TD]
[TD="width: 135, bgcolor: transparent"][/TD]
[TD="width: 117, bgcolor: transparent"]
2<o:p></o:p>​
[/TD]
[TD="width: 80, bgcolor: transparent"][/TD]
[TD="width: 71, bgcolor: transparent"][/TD]
[TD="width: 81, bgcolor: transparent"][/TD]
[TD="width: 99, bgcolor: transparent"]
3<o:p></o:p>​
[/TD]
[/TR]
[TR]
[TD="width: 207, bgcolor: transparent"] 31-45<o:p></o:p>
[/TD]
[TD="width: 107, bgcolor: transparent"][/TD]
[TD="width: 67, bgcolor: transparent"][/TD]
[TD="width: 135, bgcolor: transparent"][/TD]
[TD="width: 117, bgcolor: transparent"][/TD]
[TD="width: 80, bgcolor: transparent"][/TD]
[TD="width: 71, bgcolor: transparent"][/TD]
[TD="width: 81, bgcolor: transparent"]
1<o:p></o:p>​
[/TD]
[TD="width: 99, bgcolor: transparent"]
1<o:p></o:p>​
[/TD]
[/TR]
[TR]
[TD="width: 207, bgcolor: transparent"] 46-60<o:p></o:p>
[/TD]
[TD="width: 107, bgcolor: transparent"][/TD]
[TD="width: 67, bgcolor: transparent"][/TD]
[TD="width: 135, bgcolor: transparent"]
1<o:p></o:p>​
[/TD]
[TD="width: 117, bgcolor: transparent"]
1<o:p></o:p>​
[/TD]
[TD="width: 80, bgcolor: transparent"][/TD]
[TD="width: 71, bgcolor: transparent"][/TD]
[TD="width: 81, bgcolor: transparent"][/TD]
[TD="width: 99, bgcolor: transparent"]
2<o:p></o:p>​
[/TD]
[/TR]
[TR]
[TD="width: 207, bgcolor: transparent"] 121-240<o:p></o:p>
[/TD]
[TD="width: 107, bgcolor: transparent"][/TD]
[TD="width: 67, bgcolor: transparent"][/TD]
[TD="width: 135, bgcolor: transparent"][/TD]
[TD="width: 117, bgcolor: transparent"]
1<o:p></o:p>​
[/TD]
[TD="width: 80, bgcolor: transparent"][/TD]
[TD="width: 71, bgcolor: transparent"]
2<o:p></o:p>​
[/TD]
[TD="width: 81, bgcolor: transparent"][/TD]
[TD="width: 99, bgcolor: transparent"]
3<o:p></o:p>​
[/TD]
[/TR]
[TR]
[TD="width: 207, bgcolor: transparent"] 91-120<o:p></o:p>
[/TD]
[TD="width: 107, bgcolor: transparent"]
1<o:p></o:p>​
[/TD]
[TD="width: 67, bgcolor: transparent"][/TD]
[TD="width: 135, bgcolor: transparent"][/TD]
[TD="width: 117, bgcolor: transparent"]
2<o:p></o:p>​
[/TD]
[TD="width: 80, bgcolor: transparent"][/TD]
[TD="width: 71, bgcolor: transparent"][/TD]
[TD="width: 81, bgcolor: transparent"][/TD]
[TD="width: 99, bgcolor: transparent"]
3<o:p></o:p>​
[/TD]
[/TR]
[TR]
[TD="width: 207"] Grand Total<o:p></o:p>
[/TD]
[TD="width: 107"]
36<o:p></o:p>​
[/TD]
[TD="width: 67"]
9<o:p></o:p>​
[/TD]
[TD="width: 135"]
1<o:p></o:p>​
[/TD]
[TD="width: 117"]
15<o:p></o:p>​
[/TD]
[TD="width: 80"]
1<o:p></o:p>​
[/TD]
[TD="width: 71"]
3<o:p></o:p>​
[/TD]
[TD="width: 81"]
3<o:p></o:p>​
[/TD]
[TD="width: 99"]
68<o:p></o:p>​
[/TD]
[/TR]
</tbody>[/TABLE]

<o:p> </o:p>
I did another new Pivot, based on the samefiltering and it behaves the same, what do I need to set to only show thefiltered 68 records.<o:p></o:p>

Thanks<o:p></o:p>
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Sort by date Sort by votes
Selecting the grand total should now show any records. Do you mean double click the grand total? When you double click a power pivot table, it doesn’t work the same as a regular pivot table.
 
Upvote 0
Hi Matt.
Thanks for the response.
Yes, when I double click on the 68 grand total it open the entire "source" table the pivot was built from with all teh records instead of just showing the 68 records. If this a Power Pivot behavior, is there a setting or configuration I can use to only display the 68 records when selected by double clicking on the pivot grand total.
Thanks
Cloete Ackermann
 
Upvote 0
Yes, unfortunately that is how it works. It is not very good. You can read my article here, but it is more work and certainly not as convenient as double clicking on the total. The procedure in my article should work in Excel too.
 
Upvote 0
You must log in or register to reply here.

Similar threads

W
  • Question Question
Power Pivot Table Refresh Issue
Replies
0
Views
512
Wayne K
W
D
  • Solved
Incorrect Totals and SubTotals in Power Pivot Table when dividing
Replies
2
Views
2K
Daniel4050
D
RICH937
  • Question Question
How to create Running Totals on multiple non-date values and conduct filtered analysis on the results. Which Power Tool is the more efficient choice?
Replies
3
Views
455
RICH937
RICH937
A
  • Solved
Power Pivot Table Does not count rows with blank adjacent columns
Replies
3
Views
513
Alex Blakenburg
A
F
  • Question Question
Showing details of Pivot table from Grand total, including Row filtering
Replies
0
Views
237
Francois2France
F

Forum statistics

Threads
1,224,823
Messages
6,181,175
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top