Total Widgets needed

Question

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,

Rob

Answer 1

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:

WidgetsMembers :=
CALCULATE (
    SUM ( Projects[WidgetAmount] ),
    CALCULATETABLE (
        MembersProjects,
        CALCULATETABLE (
            'Members',
            MembersProjects ),
        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

---

Marco Russo http://ssasworkshop.com http://www.sqlbi.com http://sqlblog.com/blogs/marco_russo