3 Replies Latest reply on Aug 20, 2018 4:58 PM by Vikash Karra

Help! Calculated field based on same column but different values

Hoping someone can assist with this. What you see below is an image from 1 worksheet that I've built.  The loop qual success/cancels/unit sales are all from a column called 'Metric Name'. The actual values are from a column called 'Metric Value'. I'd like to create another calculation or to basically divide cancels and divide that by unit sales. This should be relatively easy but am having trouble doing this. I would like this calculation to be placed right after the 'cancels'. Hoping this makes sense. Basically, trying to figure out best approach on how to divide two values from the same column based on their 'metric name' and place it in the current structure. Ideally, it would be great to have the flexibility to have 1 calculated field to handle this measure (which would allow me to sort the values according to the way they want it sorted within the same worksheet)

• 1. Re: Help! Calculated field based on same column but different values

Hi,

You can try this.

SUM(IF [Metric Name] = 'Cancels' THEN [Metric Value] END)/SUM(IF [Metric Name] = 'Unit Sales' THEN [Metric Value] END)

This will be a separate calculated field and will not be part of the Metric Name field

• 2. Re: Help! Calculated field based on same column but different values

Hi, Vikash

Haven't see your sample data set, not sure what's your dimension on columns (889/908), but please find my solution below as reference for your ideas.

Hope this helps

ZZ

• 3. Re: Help! Calculated field based on same column but different values

Hey guys. thanks for the response to my initial question. I was looking to see if it's possible to not have it create another calculated field and to display that % separately,  but was wondering if you've seen how to use 1 field to get this to work. Thanks for trying  to help though!