UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Pivot Table Help, Any Version    
 
   
bazza
post Nov 15 2017, 11:10 AM
Post#1



Posts: 349
Joined: 13-February 03



Racking my brains here.

All I want is for the sum of these figures (£) to be given at the bottom of the Column. Like it is for the Quantity column.

Now when I add Sum to the field it gives me what you can see in image 2.

The value should give me £935.93 (i.e sum of all) but instead im getting this?

My source data does have blanks but I have the table filtering those out.

Any help greatly appreciated if you can point me in the right direction.
Attached File(s)
Attached File  1.png ( 2.1K )Number of downloads: 0
Attached File  2.png ( 2.88K )Number of downloads: 0
 
Go to the top of the page
 
MadPiet
post Nov 15 2017, 11:21 AM
Post#2



Posts: 2,507
Joined: 27-February 09



Sounds like you don't want a pivot table at all. Just a sum at the bottom of each of the two columns. Either that or you're leaving a lot out.

Do you have some sample data you could share? (It can be fake, just representative... and please explain the result you're looking for)
Go to the top of the page
 
bazza
post Nov 15 2017, 11:30 AM
Post#3



Posts: 349
Joined: 13-February 03



Sorry I've cropped some data out as its sensitive.

My data comes from another sheet. Sometimes it may be 12 rows of info like in this instance, sometimes it will be more and sometimes it will be less.

I can try to attach an example file if that will help.
Attached File(s)
Attached File  1.png ( 10.01K )Number of downloads: 3
 
Go to the top of the page
 
dflak
post Nov 17 2017, 05:18 PM
Post#4


Utter Access VIP
Posts: 6,231
Joined: 22-June 04
From: North Carolina


I'm with MadPiet on this issue. There may be a few pivot table questions that can be answered by looking at pictures, however most require knowledge about how the source data are organized. A sample workbook with non-sensitive data would go a long way to helping us help you. You really can't talk intelligently about pivot tables without also looking at the source data.
Go to the top of the page
 
larai
post Nov 20 2017, 03:21 PM
Post#5



Posts: 1,113
Joined: 8-February 02
From: California, USA


Hi Bazza,

I think there's some fundamentals of pivot tables that you need to review. You're using the Fee column as the data you're performing some type of evaluation (or display) against. So, in your pivot, FEE is unique. You can count how many times that specific FEE occurs in your data. You can do a SUM of some column, such as Quantity against each unique fee value.
Go to the top of the page
 
bazza
post Jan 8 2018, 10:50 AM
Post#6



Posts: 349
Joined: 13-February 03



I have a pivot table which calculates the number of times a fee occurs (Quantity) what I want to do is insert a field that will multiply the fee by the quantity. I have tried to use the calculated field option but it does not recognise "Quantity" as a valid field.

How do I go about doing this?

Thanks
Attached File(s)
Attached File  pivot.png ( 9.9K )Number of downloads: 0
 
Go to the top of the page
 
JonSmith
post Jan 8 2018, 12:15 PM
Post#7



Posts: 3,921
Joined: 19-October 10



So Bazza, your first screenshot says "36 Sum" as one of the values for "Fee", you cannot sum a column with strings it in. So thats a problem.

You do need to upload an example file however if screenshots of the Pivot sidewindow would help so we can see what fields you have dropped where.

JS
Go to the top of the page
 
MadPiet
post Jan 8 2018, 05:52 PM
Post#8



Posts: 2,507
Joined: 27-February 09



If you're using a Pivot Table, then it's using DAX under the covers, so you'd need SUMX() to do that, and not SUM()... because you can calculate something like a SUMPRODUCT that way. but you can't use SUM([Price]*[Quantity])... SUM expects a single column.

(Oh wait, that's POWERpivot...)
This post has been edited by MadPiet: Jan 8 2018, 05:54 PM
Go to the top of the page
 
bazza
post Jan 9 2018, 03:04 AM
Post#9



Posts: 349
Joined: 13-February 03



Jon

Please see sidebar attached. I omitted some of the fields from the screenshot due to sensitive info.
Attached File(s)
Attached File  pivot.png ( 11.24K )Number of downloads: 0
 
Go to the top of the page
 
JonSmith
post Jan 9 2018, 09:47 AM
Post#10



Posts: 3,921
Joined: 19-October 10



Thought so, you can only sum values. Move Fee to the values group.
Go to the top of the page
 
bazza
post Jan 10 2018, 04:12 AM
Post#11



Posts: 349
Joined: 13-February 03



Thank you for your help.

I can't believe it was that simple!
Go to the top of the page
 
JonSmith
post Jan 10 2018, 10:43 AM
Post#12



Posts: 3,921
Joined: 19-October 10



Happy to help bazza. I remember struggling to get my head around Pivot tables back in the 2003 version but I think the newer UI makes them much easier to understand but you still have to get your around some of the nuances of how they work. The differences between the 4 groups is one of them. Only values can have sums or totals etc. Its visually indicated with that greek sum symbol. The rest are for sorting/grouping/ordering.
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    23rd October 2018 - 09:01 AM