1 PivotTable from 4 tables

anatawan

New Member
Joined
Nov 18, 2014
Messages
1
Hi,

I'm pretty new to Excel 2010 Powerpivot add-in hence this question, bear with me please. I have 4 big files saved as .txt which contains the same exact fields. When I import them to Powerpivot, they are then placed on 4 separate tables. I would like to create an overview of the total performance of all 4 via a dynamic dashboard wherein the user will be able to choose either the total or individual performance based on slicers.

Ex.
filename = File1.txt
type = A product
fields = month, agent, revenue, count of sold products

filename - File2.txt
type = B product
fields = month, agent, revenue, count of sold products

filename - File3.txt
type = C product
fields = month, agent, revenue, count of sold products

required output:
slicer: Type (product), agent, month
data to be shown: total revenue, total count of sold products

i tried creating a relationship between them but it's saying that it must not contain duplicate values. B product contains data from Jan-Sep so there will be a repetition of agents showing up on the data.

i cannot copy-paste the values of each table to excel as they exceed 1.6M rows.

appreciate your assistance in clarifying this as i am a little confused with relationships.

Thank you so much in advance Power BI gods!

Regards,
Anatawan
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
This is a perfect use case for Power Query. If you don't want to do that... I would combine the files via script. If you don't want to do that...

You would want some lookup tables that are common and unique (a product master, a calendar)... then relate each of your fact tables to those lookup tables.

You would end up with measures like Total Sales := SUM(File1[Sales]) + SUM(File2[Sales]) + ...

which is not nearly as cool... but would work :)
 
Upvote 0

Forum statistics

Threads
1,224,051
Messages
6,176,088
Members
452,704
Latest member
Michael AA

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top