Hi,
I have a data table which contains financial data and a set of assumptions.
What is the best way to design a pivot table which reports the financial data BUT shows assumptions separately (I still need the assumptions displayed as part of the report). Please see attached mock-up.
I would welcome advice on the design / approach.
Thanks
Where are the ID references in the Data. Are these the values in columns L:S?
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
Hi,
Thanks for your reply.
The ID references in cols L:S in the Data worksheet are look-ups to the Assumptions worksheet.
A1=Assumption 1 and A2= Assumption 2, etc.
Apologies, just noticed minor error in my mock-up. May 2019 should shows Assumptions 1, 2 and 4 and NOT 1, 2, 3 4.
Of course becuase of the way you want to report thinhs your data is not a normalised table that makes a PT readily usable
Are you able to change your layout so that column L becomes a column in which you record an Assumption Reference A1, A2..etc and column M becomes the value 1,2,4..whatever
It means you'll have more rows in the table since where you have 3 assumptions listed in L:S you'll now have three rows in the data. However you can now use a PT in the way it's intended to be used.
Ah, I see what you mean, so my current design will not work. I know how to normalise the data so I'm ok on that part.
How do I separate assumptions from my PT as the PT should contain financial data only - is it even possible?
Not quite sure what you mean by separate a column of assumptions from your table or why it's important. The Table of Assumptions can be kept separately as you do now and the column in the Data that shows the Assumption reference can be a data validation drop down.
However please clarify if I'm misunderstanding something.
Hi,
I've had another go at this. I created a normalised table of my data (see new version attached); however, as I have created x number of rows for each assumption this in turn has increased the number of records, and reports the data incorrectly. For example, in my original data set, Beef / Calves in Apr 2019 have 3 x assumptions with a monthly cost of £20,000, but now after normalising the data the monthly cost for Apr has increased to £60,000? Also, assumptions are contained / reported in the financial data - is there no way to separate these, so PT1 = Financial data and PT2 (which is linked to PT1) shows assumptions only?
I have been thinking about this. Can Excel be used as a relational database to help me solve this problem?
Thanks for your help so far
Folks,
How do I return unique values / counts from a dataset?
See attached mock-up. I am using a Pivot Table to report on Beef for Apr 2019. This category has 3 x assumptions, so I've created 3 rows to record and report on each assumption. By doing this, my pivot table now shows a total of £60,000 (3 x £20,000), which is incorrect, as it should be showing £20,000. How do I fix this to show unique values and counts only?
Thanks
Last edited by reddwarf; 01-03-2019 at 09:28 AM. Reason: typo
If I understand correctly then, in the values field, change the field value setting from Sum of Total Cost to Average of Total Cost.
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks