Sum up values on distinct rows filter

0
votes

I need to calculate the total shipping cost for some sales orders. The dataset is as follows:

enter image description here

The issue I have is that although the shipping cost should be taken into account only once per order in the calculation, in the dataset it is repeated for each order item, thus a simple duplicates shipping costs:

=SUM(MyTable[Shipping]) = 90 // wrong value

However what I need is to:

  • filter the table to only keep 1 line for each order
  • sum up the shipping

Which should be something like:

=SUMX(FILTER(MyTable,<filter>),MyTable[Shipping]) = 35 // correct value

But I'm struggling to write the <filter>. I found DISTINCT which returns the list of unique order IDs, but not their corresponding row.

Does anybody have any ideas how I could write the filter to calculate shipping properly?

3
excelbusiness-intelligencepowerpivotdax

3 Answers

2
votes

The X functions are non intuitive but very powerful - you are on the right lines.

I would approach this with two measures, the first to sum the shipping cost and divide it by the number of rows for that order. (Key to the second half is the ALL() which opens up the context on the column referenced whilst retaining the other contexts.)

And the second to iterate that measure by order and sum the outcomes.

[Allocated Shipping] =
                      SUM ( MyTable[Shipping] )
                    / CALCULATE ( COUNTROWS ( MyTable ), ALL ( MyTable[Item] ) )

[Iterated Shipping] = 
                      SUMX(VALUES(MyTable[Order]), [Allocated Shipping])
1
votes

The simplest approach would be to use a Helper column. In E2 enter:

=IF(COUNTIF($A$1:A2,A2)>1,0,1)

and copy down. This will identify the unique values in column A. To sum these unique values, use:

=SUMPRODUCT(--(E2:E9=1)*(D2:D9))

For your data:

example

The value is 35

Naturally if the data were filtered you would use a variation of the SUBTOTAL() function or an additional helper column.

1
votes

In a very similar way to Gary's recommendation, you could use (in an additional col - E2):

=IF(COUNTIF($A$2:A2,A2)>1,D2,0)

This will show the cost of the delivery in col E itself. You can then just SUM(E2:E) to see the total cost (35).