Combine Reports Based On Multiple Join Criteria

[Hopeless Cub Fan]

New to PowerPivot, so trying to figure out how to best use this awesome tool.

I have two reports, both of which have a number of fields in common, I am trying to merge the two different reports into one using Plant/CH3/Sold-To/Ship-To/Material Group as join criteria. I have attached a screen shot of my two reports in PowerPivot.

I also want to combine the “Quantity” field in my SOI report with the “Open Orders Qty” in the BI report. What is the best way to accomplish this?

I came across what looked like a good solution at Creating lookup tables in PowerPivot using SQL queries | Javier Guillén, but it seems I need to be connected to a SQL Server instance. Thanks in advance for your help!

 

[scottsen]

If your data isn't in SQL, i would probably use Power Query to combine these into 1. If that isn't workable...

Then ya, i would try to create lookup tables for all the common fields.

[Total Quantity] := SUM(SOI[Quanity]) + SUM(BI[Open Order Qtr])

Will work fine, you just need common lookup tables (and use those in your rows/columns/slicers)

 

[PentaGalCXO]

I agree that Power Query could do the job, but I lean towards SQL whenever possible. You can import via SQL from most any database and also Text/CSV files and Excel files. I suggest not combining the SOI & BI into a single table but rather import them as seperate tables. Then build 'unified dimensions'. To build a unified Plant dimension off your tables above I would use this SQL (assuming source data is in Excel files):

select distinct * from (
select distinct Plant, Name as [Plant Name] from C:\SOI.xlsx....[Sheet1$]
union all
select distinct Plant, [Plant Name] from C:\BI.xlsx....[Sheet1$]
Union all
Select top 1 'tbd' as Plant, 'To Be Determined' as [Plant Name] from C:\BI.xlsx....[Sheet1$]
)

Note the last Select creates an entry in the Plant Dimension for SOI/BI records that have a blank or dirty values in the Plant field. I don't mean to complicate my answer here, but thought I would show how to add additional records to a dimension as needed. My assumption here is your fact tables would have a Calculated Column say PlantKey that you would use to relate to the Plant dimension. This column would handle exceptions such as blank/dirty Plant values.

Once you create & relate your 'unified dimensions' to both fact tables and define measures such as [Totale Quantity] (from scottsen above) you are good to go.

 

[Hopeless Cub Fan]

Gents,

Thank you for your input. After looking through both options I decided to enhance the two reports separately and then manually combine them via a copy & paste in Excel. I will be handing this report over to someone else to manage, and unfortunately they are not overly technical.

Below is an overview of what the final product looked like. Thanks again for your help!


Free Image Hosting