5 Replies Latest reply on Jun 12, 2018 8:14 AM by meenu choudhary

# Calculating Multiple Rows of one Column Based on the Values in another column

I know the title is kind of confusing, but I am relatively new to Tableau and the picture below and the following description should clear up what I'm after:

In the picture above there are 3 important columns: ItemChangeDetails.id, ItemChangeDetails.PricingName, and ItemChangeDetails.Pricing.Value.  What I want to do is take each ItemChangeDetails.Pricing.Value from the rows where the ItemChangeDetails.PricingName = Price then multiply that number by the ItemChangeDetails.Pricing.Value where the ItemChangeDetails.PricingName = Quantity and add that to the ItemChangeDetails.Pricing.Value where the ItemChangeDetails.PricingName = TotalLaborCost.  Ideally this would give me a column "ItemPrice" that would give me the result of the previously mentioned calculation for each ItemChangeDetails.id.  Then I would also like to add a TotalProjectPrice column which adds up all of the ItemPrices.

I have also attached the workbook that I was trying to do this in.

Could someone please show me how to do what I have described above?

Thanks,

Albert

• ###### 1. Re: Calculating Multiple Rows of one Column Based on the Values in another column

Hi ,

You can try below logic:

Create three new measure :

1. labourCost = { FIXED [ID1]: max(if [Item1]='laborcost' then [Pricing] END)}

2. price ={ FIXED [ID1]: max(if [Item1]='price' then [Pricing] END)}

3. Quantity= { FIXED [ID1]: max(if [Item1]='quantity' then [Pricing] END)}

and then the final calculation for total price: [price]*[Quantity] +[labourCost]

Note: column name used in above logic are bit different from your's. You can change it and use it accordingly.

1 of 1 people found this helpful
• ###### 2. Re: Calculating Multiple Rows of one Column Based on the Values in another column

Hi Albert,

Create a calculated field as below:

(IF ItemChangeDetails.PricingName = "Pricing" THEN ItemChangeDetails.PricingValue END) * (IF ItemChangeDetails.PricingName = "Quantity" THEN IntemChangeDetails.Pricing.Value) + (IF ItemChangeDetils.PricingName = "TotalLaborCost" THEN ItemChangeDetails.PricingValue)

Regards

Sohan.

• ###### 3. Re: Calculating Multiple Rows of one Column Based on the Values in another column

Hi Albert,

Please find my approach in the attached workbook.

Let me know if this helps or not.

~Tushar

• ###### 4. Re: Calculating Multiple Rows of one Column Based on the Values in another column

Thanks Meenu, that is just what I was looking for!

• ###### 5. Re: Calculating Multiple Rows of one Column Based on the Values in another column

Happy to help