Hello,

Create a third table containing unique batch numbers, then create relationships.

Table A * —— 1 Table C
Table B * —— 1 Table C

Br,
Anders

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

Hello,

Can you attache a file with sample data, as it is now I am just guessing how your data looks like.

Br,
Anders

Hi

I am attaching a sample which I hope will elaborate my queries.

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

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

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

Hi

Ok thanks.

Rgds

Thulasi