PowerPivot reports with consolidated report from two tables.

**nsr1989** · 12-08-2013, 01:07 PM

consolidated report from two tables containing duplicate values in key column

We have two reports(in form of excel sheets) containing same data structure(but with different values) ,containing columns

Containing -> vehicle no , Time
(where the vehicle no. will be same in both of the reports and will be repeated again and again having different value of 'Time')

How to link these two reports(excel files) to get the reports like sum/avg etc of 'Time' by 'Vehicle No.' from both the tables simultaneously.

I am not able to link these two tables with Vehicle No,as the error message keeps on popping that, Columns with duplicate values cannot be linked.
I also tried linking both the tables with a third table containing containing a table with only "vehicle No"(unique vales in this 3rd table).
bt the powerPivot is not working with this.

Thanks in advance

**cgc17** · 12-12-2013, 04:46 PM

What is the error when you try to link the first two tables to the third table with a unique list of "vehicle No"?

**nsr1989** · 12-18-2013, 04:30 PM

No error...
But after connecting the
1st table with 3 rd table
And 2 nd table with 3 rd table.

When i try to generate the pivot table report between 1st and 2 nd table ..
It says the tables do not have a relationship defined...
Please help

**cgc17** · 12-18-2013, 05:55 PM

See attached example workbook. This should do it for you.

In the Pivot Table, be sure you are using the vehicle number field from table 3 (the table with the unique list of vehicle numbers).

Also, for sum/average you'll need to create measures. In order to average, you'll need to add a counter field (just set equal to 1, so you can count how many times a specific vehicle shows up).