6 Replies Latest reply on Sep 24, 2019 7:01 PM by Hari Ankem

Show corresponding column values of a specific row based on a max value

Hi Community,

I have a rather straightforward problem.

As you can see in the screenshot below, I show the max of incremental order entry via a quick table calculation. However, the two other measures (costs and percentage) still show the sum and #. As you can see via the percentage #, there are currently 242 matching values for the filters. However, I want to only show the corresponding values to the max incremental order entry value for the other measures. So the max order entry is 594 -> I want to show for costs and percentage the corresponding values for that row based on max order entry (in this case 594). So if for 594 max order entry, costs were 1.000.000 and percentage 5%, I want to show these values. So costs and percentage should show the corresponding row values based on the max order entry value.

It should basically work like a vlookup function in Excel where you give out corresponding column values from a specified row based on a specific column value (in this case order entry). • 1. Re: Show corresponding column values of a specific row based on a max value

Hi Christian,

I think there are different ways to approach this problem. Maybe what I'd do would be to add a filter like a rank(max incremental value)=1 or a Top 1 by max incremental value. If you attach a workbook we can provide other solutions if you need.

Hope this helps. If does, please mark it as helpful and correct so other users can refer to it. Many thanks!

Best,

Diego

• 2. Re: Show corresponding column values of a specific row based on a max value

Sadly, for now, I cannot provide a workbook.

Normally, it the solution should be an additional filter as the following: Filter: max OE = incremental order entry -> show only true values.

However, this doesn't work as he says this compares aggregates with non aggregates and when I put it in {} it allows the calculation but it has no true values -> max OE is never equal to incremental order entry.

I simply want to display the corresponding values of the columns from the same row as the max OE.

• 3. Re: Show corresponding column values of a specific row based on a max value

See if this helps:

The image below displays the order details for every customer. The image below displays the most recent order date, and the sales for the corresponding order. Note that there are no filters added as such. Here are the two calculated fields:

1. Max Order Date: {FIXED [Customer Name]:MAX([Order Date])}

2. Sales of Most Recent Order: {FIXED [Customer Name]:SUM(IF [Order Date]=[Max Order Date] THEN [Sales] END)}

The workbook is attached.

• 4. Re: Show corresponding column values of a specific row based on a max value

Hi Hari,

hi Diego,

Thanks for your replies. However, it is still not quite working. I have attached a dummy workbook that showcases the problem.

In the dummy workbook, costs and percentage should show the associated costs / percentage for the Max OE (20). Thus, costs should show 498.092 and percentage should show 0,03.

Best regards,

Christian

• 5. Re: Show corresponding column values of a specific row based on a max value

Hi Christian,

Please find the example attached. I created the following calculation:

Percentage: if [OE]={MAX([OE])} then [Percentage] END

Costs: if [OE]= {FIXED :MAX([OE])} then [Costs] END

If this helps you or answers your question, please mark it as helpful and correct so other users can refer to it. Many thanks!

Best,

Diego