Combining multiple datasets in PowerPivot

Question

Essentially what I want to do is to create an interactive dashboard using slicers in Excel 2010 PowerPivot.

I have 3 different data sets from a sql database which share "dimensions."

1. Incoming Orders
2. Sales Order Backlog - essentially what products are currently in production and waiting to ship or invoice
3. Invoiced Sales

Each data set shares certain "dimensions" such as Customer, Product, Time Period, Category, etc.

I want to have a specific chart for each of the business processes and have the slicers filter each chart at the same time. When someone chooses ACME Rockets as a customer, I want all three charts to update and show only information about ACME Rockets.

I could probably accompish this wiht a union statement and then hard filter each chart only to show it's subsection of the data, but I was hoping for something more intuitive.

Can anyone point me toward how I would accomplish this with PowerPivot?

Answer 1

Hi David! It the tables are related to the fact tables in the underlying model then it should be really easy to have shared slicers. What you have to do is "connect" the slicers representing shared dimensions to all pivottables/charts that you are interested in. Example: 1. insert pivot chart on sheet1 2. add a shared dimension attribute to the slicers pane 3. insert another pivotchart to the same sheet (sheet1) 4. add any measure from another fact that you are interested in slycing to the values pane for that chart 5. select the slicer representing the shared dim attribute 6. click on the "Options" tab and then on "PivotTable Connections" or right-click on the slicer and click "PivotTable Connections" 7. put a checkbox on all pivottables that you want for this slicer to "slice" :) - in this example it would be "PivotTable1 - Data for Sheet1 Chart1" or something like that

Additionally check a great powerpivot resource by Rob Collie (@powerpivotpro) at http://powerpivotpro.com for examples of using a main sheet with all slicers connected to all other pivots on other sheets.

Best regards,

@HrvojePiasevoli