5 Replies Latest reply on Dec 15, 2016 9:46 AM by mozammel chowdhury

# Based on Highest Amount Choose that identical Row

Hello I have a situation where I am needing to only retain the column that has the highest value. I am attaching an example file for better understanding. So in the file A,B,F and G has multiple category and multiple amounts. The rest has only one. The tableau function should lookup the amount and see which one is the highest for that desc and retain only those rows. The spreadsheet shows and Initial view and a Final view as to what I am referring to. Any help will be appreciated

• ###### 1. Re: Based on Highest Amount Choose that identical Row

This can be done using table calculations. See attached for an example using RANK().

• ###### 2. Re: Based on Highest Amount Choose that identical Row

Thanks Stephen Rizzo . However I already knew about the Rank function. With the Rank function I can sort. It does not get me what I am looking for. Essentially I need a function that will eliminate the  duplicate row item with the lowest Amount. So in the example the "Desc"

A, B, F and G with the lowest value will get removed/filtered automatically and the display will remain with the highest value row.

Thanks

• ###### 3. Re: Based on Highest Amount Choose that identical Row

Maybe I misunderstood what you are trying to do - although I can't double check right this minute, I thought the workbook I attached to my previous post replicated your "final state" table. Could you clarify what exactly you are trying to create?

• ###### 4. Re: Based on Highest Amount Choose that identical Row

Hi Mozammel,

I think you can get what you want by a simple LOD based filter.  We'll call it Max Desc Amt.

Max Desc Amt:  [Amount] = { FIXED  [Desc]: MAX([Amount]) }

This field should be a dimension returning T or F.  Drag to filters and set to T.  Let me know if you have any questions.

You should be able to get the same result using Rank as Stephen suggested but this is an alternate approach.

Regards,

Ivan

LOD Example

Rank Example

1 of 1 people found this helpful
• ###### 5. Re: Based on Highest Amount Choose that identical Row

Thanks @ IVAN Young

for the solution. This is what I was looking for.