5 Replies Latest reply on Jan 7, 2013 2:14 PM by Shawn Wallwork

# How to return value at a corresponding maximum value?

I have an Excel file with multiple rows of hourly data.  Lets assume that column one is "Date/Time", column two is "Sales" and column three is "Profit Ratio".  How would I create a formula to return the "Profit Ratio" at the maximum "Sales" value?

• ###### 1. Re: How to return value at a corresponding maximum value?

Craig, here's the workbook you describe using Superstore Sample (in the future please set this up yourself). So what do you mean when you say 'at the maximum Sales value'? Maximum how? Of all time? Of all months? Days?

--Shawn

• ###### 2. Re: How to return value at a corresponding maximum value?

Hello Shawn.  Thanks for your response.  I am new to Tableau and I will make sure I include the workbook next time.  For the Superstore example I would like to determine the maximum value in the "Sales" column each month and display the maximum sale and corresponding "Profit".  Its easy to display the maximum profit but what I am interested in is the profit for each maximum monthly sale.

Craig

• ###### 3. Re: How to return value at a corresponding maximum value?

Pinging Jonathan or Alex. Craig this is going to be some sort of IF/THEN that includes a LOOKUP table calculation -- beyond me.

--Shawn

• ###### 4. Re: How to return value at a corresponding maximum value?

I used a filter on index() set to 1 with month on the left of the advanced compute (could be considered our 'headers') and everything else on the right. The partition is then ordered with sales descending so for every month index() of 1 is the top selling Order ID.

You get to this by right clicking index on the filter, edit table calculation

EDIT: as you are new to Tab, I'm sure the index and table calculations may be a bit daunting - please ask if you need help. If your data looks radically different, we would need a sample (with random numbers if sensitive)

• ###### 5. Re: How to return value at a corresponding maximum value?

I so wouldn't have gone that direction, obviously. Thanks Alex.

--Shawn