Hi, I just started using Powerpivot for excel 2010 and I think it's very cool and handy.
I have this complex (at least for me) report that involves several tables done in normal pivot and works fine but i find it limited to what I want and it's hard to maintain due to the data sources.
The primary source is a .csv source that has approximately 80,000 rows and about 20 columns (for each month). The main columns are:
Client number
Client name
product code
container code
containers delivered
containers returned
In an excel file I have four tables, each with unique records that give me the city, container code group, country zone, and business unit, based on the main columns.
The business unit table has all the product codes and each has a corresponding business unit: IG or HH. However, one product code "3000" is the only code that belongs to IG business unit, but it also belongs to HH for some clients.
In the normal table I had a column with an IF statement that evaluated if the code is 3000, then it performed a lookup in another table that had the client numbers that have the product code assigned to them.
So in the normal pivot table i have sliders that filter by: country zone, business unit, city, conatiner code group and product. So if i choose product 3000 that is mainly for IG, I can see which clients for HH has them also. In powerpivot I'm stuck and don't know how to achieve this result.
Any ideas?
Hope I explained myself jeje.
Emilio
I have this complex (at least for me) report that involves several tables done in normal pivot and works fine but i find it limited to what I want and it's hard to maintain due to the data sources.
The primary source is a .csv source that has approximately 80,000 rows and about 20 columns (for each month). The main columns are:
Client number
Client name
product code
container code
containers delivered
containers returned
In an excel file I have four tables, each with unique records that give me the city, container code group, country zone, and business unit, based on the main columns.
The business unit table has all the product codes and each has a corresponding business unit: IG or HH. However, one product code "3000" is the only code that belongs to IG business unit, but it also belongs to HH for some clients.
In the normal table I had a column with an IF statement that evaluated if the code is 3000, then it performed a lookup in another table that had the client numbers that have the product code assigned to them.
So in the normal pivot table i have sliders that filter by: country zone, business unit, city, conatiner code group and product. So if i choose product 3000 that is mainly for IG, I can see which clients for HH has them also. In powerpivot I'm stuck and don't know how to achieve this result.
Any ideas?
Hope I explained myself jeje.
Emilio
