98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel Training and help » Pivot Table
Pivot Table
Resolved · Medium Priority · Version 2016
Wed 4 Dec 2019, 12:52Delegate Alicia said...
Alicia has attended:
Outlook Advanced course
Excel Advanced course
Access Intermediate course
Pivot Table
Hi,
Hope you're well.
I have a quick question. I've been trying to build a dashboard and I'm trying to make a calculated field to calculate the percentage of the two columns beside it.
The original data is text rows (eg. student records). I want to find out the % of the fields without a certain field, which is how I analyse the numbers for this task.
XXX || xxxxx || first two columns as a %
data 1 || data 2 || (data 2/data 1 calculation goes here)
How would I do this? I don't want to do this manually, as I want this calculation to be contained within the pivot, so that its dynamic like the rest of it. I've looked it up online but it isn't working like the tutorials show me.
I hope this makes sense and thanks in advance.
Best regards,
Alicia
Wed 4 Dec 2019, 13:06Trainer Jens said...
RE: Pivot Table
Hi Alicia,
Thank you for the forum question.
Make sure your Pivot Table is selected. In the Calculations group on the Analyze tab click Fields, Items & Sets and click Calculated field. Type the heading and click in the Formula box. Double click the field names in the Fields box to create the formula in the formula box. =field 1/field 2
I hope this makes sense
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Wed 4 Dec 2019, 13:49Delegate Alicia said...
RE: Pivot Table
Hi Jens,
Thanks so much for getting in touch.
I have done this, however now the values are just 0.
For your reference, the data isn't numbers, its text.
Will this be possible to do?
Thanks
Alicia
Wed 4 Dec 2019, 14:11Trainer Jens said...
RE: Pivot Table
Hi Alicia,
Sorry I didn't see that it is text. Calculated fields can only be done from numbers.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Wed 4 Dec 2019, 16:38Delegate Alicia said...
RE: Pivot Table
Hi,
Thanks for your response. Is this the case, even when the pivot table generates numbers from the text? Because ideally I would like to calculate % from a calculation of two generated numbers.
I hope that makes sense
Best
Alicia
Wed 4 Dec 2019, 16:53Trainer Jens said...
RE: Pivot Table
Hi Alicia,
A Pivot Table can only count text. I am not sure I understand what you mean by the Pivot Table generate numbers from text. If is count you have from text strings a calculated field cannot divide the two columns. It can only be done if the source is numbers.
I would like to have a look at your file.
If it is possible send it to:
info@stl-training.co.uk
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Thu 5 Dec 2019, 19:04Trainer Jens said...
RE: Pivot Table
Hi Alicia,
Thank you for sending the file.
It is as I wrote Pivot Tables can only count text. It display numbers in the Pivot Table but it is still text strings which cannot be calculated.
You will need to add columns to your source data with number values if you want to do divisions.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Fri 6 Dec 2019, 10:24Delegate Alicia said...
RE: Pivot Table
Hi Jens,
Thank you for responding and reviewing the file.
Is there a way of automating the process at all, or do I just have to input each number into a new column? Do you have any suggestions as to how I could make a divisible column, while still making it dynamic?
Best regards
Alicia
Mon 9 Dec 2019, 22:49Trainer Jens said...
RE: Pivot Table
Hi Alicia,
There is only one solution and it is to use Power pivot and DAX (data analysis expression).
I have attached your file with the DAX but you need to activate Power Pivot in Excel to see the DAX code.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Attached files...
For STL2.xlsxMon 16 Dec 2019: Automatically marked as resolved.
Training information:
See also:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Colouring cells containing formulasCells in a worksheet can contain values or they can contain formulas. You may wish to identify all the cells in your worksheet that contain formulas by colouring those cells. |
