Tuesday, January 01, 2013 3:05 AM
Using PowerPivot how do I determine how many widgets need to be on hand. (I can find the answer in excel using a huge matrix but it literally takes hours to solve. )
The company has tens of thousands of projects going at once - The projects have anywhere between one and fifty people assigned to them. The same people can be assigned to multiple projects. Frequently, a project is finished and the team will move on to the next project (not all team members need to be present for a project to be completed.). If the whole team is called in to do a project - we need to know how many widgets must be on hand to complete every project that was assigned to each team member? A separate 'lookup' table is available showing the number of widgets associated with each project.
Here is a simplified example
Project 1 - John
Project 2 - John and Bill
Project 3 - John and Sue
Project 4 - Kathy and Bill
Project 5 - Sam
For simplicities same, assume that each project needs 10 widgets (in reality they will change according to what is in the 'lookup' table) - the answers should be as follows
Project 1 - 30 widgets - because John is associated with Projects 1, 2 and 3
Project 2 - 40 widgets - because John and Bill are associated with Projects 1,2,3 and 4
Project 3 - 30 widgets - because John is associated with Projects 1,2 and 3 - Sue does not add to the requirement because she is not associated with any other projects
Project 4 - 20 widgets - because Bill is associated with Projects 2 and 4 - Kathy does not add to the requirement because she is not associated with any other projects
Project 5 - 10 widgets - because Sam is associated with this project alone.
Any help would be GREATLY appreciated,
Tuesday, January 01, 2013 5:17 PM
This is a many-to-many relationship pattern that has to be used back and forth in the same expression. Not intuitive at first, but can be done in DAX.
Imagine you have these tables:
- Projects (ProjectId, ProjectName, WidgetAmount)
- Members (MemberId, MemberName)
- MembersProjects (ProjectId, MemberId)
Having two relationships (Projects-MembersProjects and Members-MembersProjects) you can write this measure:
SUM ( Projects[WidgetAmount] ),
ALL ( Projects )
You can find a longer and detailed explanation at http://www.sqlbi.com/articles/many2many/ even if I think we didn't cover this particular scenario in the whitepaper.
Marco Russo http://ssasworkshop.com http://www.sqlbi.com http://sqlblog.com/blogs/marco_russo