Hi,
I want to create a pivot report that shows Actual v Budget v Forecast data. The data for each category is held in different Excel files. I can import the files in to the data model in a new file so I have a table for each.
Is it possible using Power Pivot to create a pivot table that pulls in the value data from each table? I have tried creating relationships between the tables but it says that they contain duplicate values and can't be set up.
I have attached a zip file that contains the three files and the file containing the data model that I am trying to report from (Bax_PPivot_Combine.xlsx).
My question is just trying to find out what is possible with Power Pivot. I know I could use Power Query to combine and merge the files in to one table.
Thanks as always
Bax
Hi Bax,
You can either use Power Query to combine the 3 tables into one table and load that to Power Pivot, or you can create dimension tables for the sub-category field and date field and then create relationships between the fact tables (actual, budget and forecast) and the dimension tables. You then use the dimension table fields in your row/column labels and the value fields from the fact tables.
I think given your recent questions on the forum that it would be beneficial for you to learn Power Pivot and DAX.
Mynda
