Hello - I’m trying to make a P&L statement from a trial balance in PowerPivot vs. the historical lookup method. In a simple example, this was easy to do in PowerPivot, but now that I’m trying to add in a pairing that needs to occur I cannot create the relationships.
In my simple example I have three tables:
1) P&L Hierarchy.....three columns “Level I”, ”Level II”, “Level III” which represents a hierarchy of multiple Level IIIs roll up to a Level II, and then multiple Level IIs roll up into a Level I.
2) Trial Balance Extract.....four columns “$”, “Date”, “GL #”, and “Department”. These are all the transactions.
3) P&L Mapping....three columns “Level III”, “GL #”, and “Department”. This table is made to show the combonation of GL # and Department which pair together to drive which Level III of the P&L the combinations go to.
However, for my third table P&L Mapping, I do not have a unique value as many Departments can go to a GL# and then the Level III is repeated numerous times. The same happens with GL #s as they are not unique with the pairings necessary either.
Do I need to create a Lookup with an AND to get this to work, or is there a way in PowerPivot with relationships to do this?
In my simple example I have three tables:
1) P&L Hierarchy.....three columns “Level I”, ”Level II”, “Level III” which represents a hierarchy of multiple Level IIIs roll up to a Level II, and then multiple Level IIs roll up into a Level I.
2) Trial Balance Extract.....four columns “$”, “Date”, “GL #”, and “Department”. These are all the transactions.
3) P&L Mapping....three columns “Level III”, “GL #”, and “Department”. This table is made to show the combonation of GL # and Department which pair together to drive which Level III of the P&L the combinations go to.
However, for my third table P&L Mapping, I do not have a unique value as many Departments can go to a GL# and then the Level III is repeated numerous times. The same happens with GL #s as they are not unique with the pairings necessary either.
Do I need to create a Lookup with an AND to get this to work, or is there a way in PowerPivot with relationships to do this?
