Hi,

I'm using Excel 2013. I want to combine two sets of data in a pivot table and am trying to use Power Pivot to do so. My problem is that one worksheet has duplicate values in the column I'm using to create a relationship...

Worksheet 1: Hourly Sales (Actual)
Worksheet 2: Daily Sales (Target)

Both tables have a Date and Sales Person column. I want to link the data on these two items... I brought both data sources into Power Pivot (as two tabs) and created calculation fields to merge the Date and Sales Person fields. However, the Hourly Sales source has duplicate values as a Sale Person will log several instances under the same date. I don't need this level of detail in my final report so how can I aggregate my Hourly Sales into a single daily figure so that I can then create a relationship with the Daily Sales targets?

I tried creating a pivot table in my Excel file of the aggregated Hourly Sales data and then bring this into Power Pivot, but it won't let me create a Power Pivot from a pivot table.

Is there a way to create an aggregation in Power Pivot? Or perhaps there's another way of doing this?

Here's an example of my two worksheets:

Worksheet 1: Hourly Sales (Actual)

Date Time Sales Person Actual Sales Key
01/08/2018 09:00 Bob 1,000 Key
01/08/2018 10:00 Bob 1,500 Key
01/08/2018 10:00 Sally 500 Key
01/08/2018 11:00 Sally 600 Key


Worksheet 2: Daily Sales (Target)

Date Sales Person Target Sales Key
01/08/2018 Bob 2,000 Bob>01/08/2018
01/08/2018 Sally 1,000 Sally>01/08/2018

Note: The 'Key' shown above isn't actually in my worksheet, I created it as a calculated field in the Power Pivot.

Thanks!