Is there a Measure equivalent to Sumproduct?

[Tytalus]

Hi. Totally new on the boards and just got into PowerPivot. My problem is that I'd like to create a measure that does something very much like SUMPRODUCT. The wrinkle is that one of the arrays to be factored into the SUMPRODUCT is pulled from a related table. Here are the particulars.


I have a table of personnel that includes their firms and their Direct Labor Rate (DL).


I have a table of firms that includes their Overhead (OH), Fee on Labor (FL) (note that both these variables vary by firm).


I have a table of Tasks where each line is a task item (WBS), a person from the personnel table, and an assigned number of hours (h).


So the relationships should be straight forward.


So the cost of any task for any specific person is (h)x(DL)x(1+OH)x(1+FL). Also, it will be useful to track each component of the cost calculation as a separate item. What I'd like to be able to do is PowerPivot the sums of costs or cost components (total costs, OH, FL, etc) sliced by tasks and/or firms.


I realize I can use calculated columns to import the particular multipliers into the task table and just sum these calculated columns in the pivot, but it would be great to do this in a measure too.


So here are the two questions: If I had calculated columns in tasks that pulled in the individual person's rate, Firm OH, and Firm Fee, is there a sumproduct equavalent measure that I could do to multiply the columns together to get costs in the filtered context? Assuming that is possible, is there a way to do this without the calculated columns?


If it can't be done I can certainly live with calculated columns, but I'm thinking I might be able to gain performance by doing this in a measure. OTH, I'm wondering if the act of having a measure pull the data from related tables in order to 'synthesize' an array for the SUMPRODUCT is just as much system overhead as the calculated column in the first place anyway.

Thanks in advance for everyone's thoughts.

 

[Jacob Barnett]

Tytalus, welcome to the Revolution!

There certainly is an equivalent and its called SUMX. It basically iterates another expression over a table and adds all the answers together. Its generally best to write these things in 2 measures, one for the basic expression and one for the iteration. In your case this could look like:

[Raw Personnel Cost]=SUM(Tasks[Hours])*sum(Personnel[DL])*(sum(Firm[Fee on Labour])+sum(Firm[Overhead])+1)

[Iterated Personnel Cost]=SUMX(Tasks,[Raw Labour Cost])

Note that the SUM()s in the first measure are just there to satisfy DAX's requirement that you can't present a 'naked' column - because of the iteration later they could have been AVERAGE(), MIN() or MAX() and it wouldn't have effected the answer as once you get to the iteration the function does the calculation for every row of the Tasks Table. Not only do I think that this is probably more powerful that SUMPRODUCT() but it has a bunch of siblings as well; AVERAGEX(), MINX(), MAXX(), COUNTX() etc.....

Funnily enough I'm not sure that the relationships are quite as simple as they appear at first glance based on the data structure you mention in your post. I made a 2 minute model of your data so I could test the formula which I saved here: https://docs.google.com/file/d/0Bz5yMU2oooW2MEE0QXNqekd2MHc/edit?usp=sharing.

Normally I would advocate using a 'Star Schema' type scenario where by Dimensions (Firm/Personnel) are each related to a central fact table(s) (Tasks).

This wasn't possible here as your fact table only has Person not their firm. That mean't I had to relate Firm to Person and it wasn't totally intuitive to me that the structure would work but that's the beauty of PowerPivot, when you select something from the Person table that automatically filters the Firm table to which it is related. The upshot is that not only does the measure work but the slicing is perfect despite it feeling like you are a relationship short.

Hope this helps.
Jacob

 

[Tytalus]

Thanks Jacob. That pretty much does the trick. You're understanding of the relationships is correct. Going through this problem actually gives me a better intuitive understanding of "X" DAX functions than going through the relevant section of Rob Collie's book.

The funny thing is that I don't think I'll ever have a use of the non-X (using the regular aggregate, non-iterative functions) measures that the sumx measures I really need depend on, but I'm stuck with them because 'nesting' the original non-X measure expression directly into the sumx measure doesn't work. Thinking about the nature of filtered contexts and iterative contexts this actually makes sense. Normally its second nature to think that nesting functions, this way should work - but now I need to develop an inner instinct for the particular ways in which non-iterative and iterative aggregations can't necessarily mix within a single DAX expression.

Once I get this thing up and running in it's full complexity and size, I'll try a different version with the calculated columns and do an system overhead comparison. I suspect that it's not going to be very different, since the calculated formulas iterate across all the records to get their individual values - and the iterative formula pretty much does the same thing. So I think the number of operations necessary to get the answer might be the same.

Thanks again.