Seriously missing some PowerPivot concept...

[mdrew9]

I cannot get this to work. I have a source table which is a dump from a clients SAP system. On a simple level lets just say it has

tblSAP(Project Number, Account Numbers, Dates, Month(#), Year and Charges)

I have created other tables which have more data for grouping and reporting

tblDateGrouping (Month, Quarter, Month Name)

tblProjects (Project Number, Project Name, Project Type)

tblDescription (Account Number, Account Description (ie, Real Estate, or Operations)

I create relationships between tblSAP AccountNumber, Month#, Year to the other tables. But I do not add columns as =related() in tblSAP.

Now I want to create a pivot table that is built like tblProjects(Project Name), tblDescription(Account Description), tblSAP(Charges) by tblDateGrouping (Month Name)

This does not work. I can tell it doesn't work because when I create this pivot and I put tblProjects(Project Number) and tblSAP(Project Number) I get many tblSAP(Project Numbers) for each tblProjects(Project Number), where project 1001 should only = 1001.

The only way I can get this to work is by adding =related() columns for each "vlookup". But doesn't this defeat the purpose???? What am I missing???

 

[Laurent C]

Dimensions should be filtered if you add a measure from (tblSAP) to your pivot table.

 

[JavierGuillen]

by default, if you place only data in your pivot table 'row labels' you would get *ALL* possible combinations. As Laurent stated, placing a numeric value in the 'values' section will generate an effect in which the pivot table automatically filters out all the other combinations for which there is no data. As such, is always a good practice to place values first and then row labels later.