5 Replies Latest reply on Jun 6, 2017 10:49 AM by SANDIP SHARMA

# Calculate data by tracking number instead of product number

I'm trying to analyze some data at the shipment ID level (tracking number) instead of the product ID level but the data is at product ID level (each line item is a product ID). This wouldn't be a problem if the data contained 1 product per shipment but there are often multiple products per shipment, so tableau is running all my calculations at the product level instead of the shipment level (what I actually need) and duplicating tracking numbers. For example, I want to calculate the cost of a shipment - if the shipment cost \$10 and there were 3 products within that shipment, tableau will calculate this tracking number as costing \$30 instead of \$10 since each line item is assigned a shipping cost of \$10.

What's the best way to go about filtering or modifying my calculations to look at the distinct shipment ID instead of at the product level?

• ###### 1. Re: Calculate data by tracking number instead of product number

When you add the shipment cost to your viz, change the aggregation from SUM to MIN by clicking the down chevron -> measure -> Minimum.

• ###### 2. Re: Calculate data by tracking number instead of product number

Hi Jon

Can you share your workbook - if not could make some dummy data to share -

the solution to your problem can take different approaches depending on how the data is structured - are there multiple data sources/ and if so how are they connected -

if the tracking number is part of each product record so that 3 records each include the tracking number and \$10 - then you can just take the average to aggregate the cost and not sum -

if the shipping cost is embedded in a "Shipping Record" and the Product ID is part of an BOL  record the approach would be different

Jim

• ###### 3. Re: Calculate data by tracking number instead of product number

Also, not seeing the workbook, I am assuming that your are able to get distinct Shipment IDs.

• ###### 4. Re: Calculate data by tracking number instead of product number

Thanks Brian, this would work for sum but what about averages?

• ###### 5. Re: Calculate data by tracking number instead of product number

HI Jon,

Use LOD for such kind of problem, Since your Shipment ID is not Repeating but one Shipment can have multiple product due to this your your shipment value is getting multiplied by number of rows or product  for that shipment.

{Fixed Shipment: Sum(Shipment Value)}

in this case you are calculating your data by each shipment. Your output will be as per your expectation, From here on you can use SUM , Average, whatever aggregation you want to perform.

In My Below Example, My Shipment ID S001 is repeating three times due to Products, actual cost is 10 but due to repetition it is showing 30 highlighted in blue circle. But My Expectation is to get 10, so wrote formula

{ INCLUDE [Shipment ID]:

MIN({ FIXED [Shipment ID],[Product] : SUM([Amount])})

}

or

{ FIXED  [Shipment ID]:

MIN({ FIXED [Shipment ID],[Product] : SUM([Amount])})

}

Actual output is highlighted in Yellow as 10.

Hope you are looking for such kind of logic. Now you can use this logic as per your requirement.

1 of 1 people found this helpful