Log In
Register
Home
November 27, 2020
Offline
Hi
I have two transaction tables
1. Table A - Date, Batch number, Sales
2. Table B - Date, Batch number, Product, Discount
I want to create pivot table with product wise sales and discount. Unfortunately Product column is not in Table A which has sales values.
I dont want to merge two tables as the data is huge.
Can I achieve the result without merging the tables? Pls suggest. Thanks
VIP
Trusted Members
December 7, 2016
Offline
Hello,
Create a third table containing unique batch numbers, then create relationships.
Table A * —— 1 Table C
Table B * —— 1 Table C
Br,
Anders
November 27, 2020
Offline
Hi
Thanks for the reply.
Yes, I created a third table with unique batch numbers.
I want product wise sales. Product column is not in Table A - fact table, which contains sales value.
So I am unable to pull that sale info by product wise.
I need a report like this:
Product
Batch No. Sales Discount
Hope I am clear.
Thulasiraman
VIP
Trusted Members
December 7, 2016
Offline
Hello,
Can you attache a file with sample data, as it is now I am just guessing how your data looks like.
Br,
Anders
November 27, 2020
Offline
Hi
I am attaching a sample which I hope will elaborate my queries.
VIP
Trusted Members
December 7, 2016
Offline
Hello,
Relations in Power Pivot is not the same as relations in a relational database. I have made some adjustments, in so making Table B a fact table and added Tables C and D as dimension (lookup) tables, see attached file.
Table A[Process] and Table B[Process] has a many to one relationship with Table C[Process].
Table A[Lot No] and Table B[Lot No] has a many to one relationship with Table D[Lot No].
When building the Pivottable you add Process from Table C and Lot No from Table D in the Rows section and then you add the Sales from Table A and the Cost from Table B in the Values section.
I hope this gives the answer to your query.
Br,
Anders
November 27, 2020
Offline
Hi
Thanks for your reply and info. It seems that you have added "Process" column in Table B manually.
And then created two tables - C and D - with Lot No and Process as master files.
Adding the process column manually in Table B is herculean task as it contains thousands of rows!
Is there any other way to accomplish this?
Thanks
Thulasi
July 16, 2010
Offline
Hi Thulasi,
You can and should be using Power Query to get your data and load it into Power Pivot. In Power Query you can create queries to extract distinct lists of Processes and Lot Numbers automatically, so that it's not a herculean task.
You can learn Power Query here.
Mynda
1 Guest(s)
