How to sum fields that are of a different grain in the same table.

Question

SQL 2008 R2, PowerPivot 10.50.2500

I have a table in PowerPivot that contains dollar amounts at both the Invoice level (parent) and Invoice Line Item level (child).  A listing of fields is below to illustrate the composition of the PP table.

invoice.InvoiceId

invoice.ShippingCharges

invoiceLineItem.ProductName

invoiceLineItem.QuantitySold

invoiceLineItem.SalesAmount

Since the ShippingCharges would be duplicated for each line item for the invoice, what formula could I use to get the true actual total of ShippingCharges?  If I do a straight SUM, it obviously yields a much large figure ( a single invoice with 5 line items that had a $10 ship charge would yield $50).  I tried variations of SUMX, but I could not get this to work.

I know of alternatives such as rearchitecting the table structure and even the base query, but if there is a quick and simple solution here, that would be great.

Thanks!

Answer 1

A quick solution could be to average instead of sum.

---

Javier Guillen
http://javierguillen.wordpress.com/

Answer 2

Thank you, I will try this out.

Answer 3

Unfortunately, although it comes close it is not accurate.  I could be doing something wrong, but seems straight forward.

Here is my formula within the Pivot report:

=SUMX(DISTINCT(Sales[InvoiceId]), AVERAGE(Sales[ShippingCharges]))

Answer 4

Juan -

You could group by InvoiceID and get the max shipping charge.  Then sum the maxs.

First create a calculated measure to get the MAX:

MaxShippingCharge:=MAX(Sales[ShippingCharges])

Then use SUMMARIZE to do the grouping, and wrap that in a SUMX:

SumxMaxShippingCharge:=
SUMX(
     SUMMARIZE(Sales
               ,Sales[InvoiceId]
               ,"ShippingCharge"
               ,[MaxShippingCharge]
              )
     ,[ShippingCharge]
    )

That will give you this (sample data at the top; pivot at the bottom with the desired result in the measure "SumxMaxShippingCharge"). The Grand Total sums the maxs to 30 as desired.

Let me know if that satisfies the requirement.

---

Brent Greenwood, MS, MCITP, CBIP
// Please mark correct answers and helpful posts //
http://brentgreenwood.blogspot.com

Answer 5

Juan -

Apologies.  Just remembered you're on v1, so you don't have summarize.

Couple of other ideas.  One would be, assuming you have line item numbers (1,2,3,etc), only sum the shipping for rows with line item 1.  If you don't need to filter below the invoice level (e.g. filter out particular products), that approach could work for you.

Another approach is to force the shipping to be allocated across the line items.  Essentially weighting the shipping charge by the number of line items in the invoice.

First you'd need a count of line items per InvoiceID.  Create a calculated column like:

=CALCULATE(COUNTROWS(Sales),FILTER(Sales,Sales[InvoiceId]=EARLIER(Sales[InvoiceId])))

Then create a calculated measure that divides the invoice's shipping charge by the number of line items:

WeightedSum:=SUMX(Sales,Sales[ShippingCharges]/Sales[ItemCount])

That would give you the "WeightedSum" measure below:

Hopefully that helps. 

---

Brent Greenwood, MS, MCITP, CBIP
// Please mark correct answers and helpful posts //
http://brentgreenwood.blogspot.com