# locate a row based on a column value

• ### Question

• User529902532 posted

Hi,

I need to aggregate a column (Count)  by looking at another column (Percentage).

Percentage       Count

50%                 15

60%                 30

70%                 20

----------------------------------

70% (Max)        20

As the aggregates, I look for the max of Percentage and trying to get the Count value on that row.

Is there a way to do this?

Thanks.

Tuesday, July 5, 2011 3:12 PM

• User1471008070 posted

Okay, now you can get the max value, you still want to get the acount value which belongs to the max value, right? If so, I think you can create a dataset which is used to retrieve acount value corresponding the max value. Then create a parameter with hiden property whose value come from this dataset, then at the footer of table, you can utilize this parameter to display the acount value 20.

If you have any question about the steps, please feel free to let us know.

• Marked as answer by Thursday, October 7, 2021 12:00 AM
Monday, July 11, 2011 10:57 PM

### All replies

• User191633014 posted

use:

```declare @tbl as table(Percentage int,  "Count" int)
insert into @tbl
select 50,15 union all
select 60,30 union all
select 70,20

select [count]
from @tbl
where Percentage = (select max(Percentage) from @tbl)```
Tuesday, July 5, 2011 3:25 PM
• User529902532 posted

Thanks for the reply.

But I have an mdx query, not a sql.

And I wanted to know if this is possible in tablix stucture.

Regards.

Tuesday, July 5, 2011 3:57 PM
• User1471008070 posted

Hi,

Based on your information, I would recommend you achieve the percentage and Max value on report level, you can add another column to calculate the percentage the expression like this sample =Fields!OrderQuantity.Value/Fields!SalesAmount.Value

If you want to get the max value, you can type in the expression =MAX(Fields!OrderQuantity.Value/Fields!SalesAmount.Value) at the footer row of the tablix

If I misunderstand you, please feel free to let me know.

Thursday, July 7, 2011 5:23 AM
• User529902532 posted

I guess I couldn't describe what I need exactly.

All I need to do is, get the row with maximum percentage and get some other column values from that row.

In the example above, I can find the maximum of percentage with =Max(Fields!Percentage) which is 70%

But in the footer, I also have to display the Count value that is on the same row with that max percentage which is 20.

I don't know how to get that value.

Thanks for your help.

Thursday, July 7, 2011 11:14 AM
• User1471008070 posted

Okay, now you can get the max value, you still want to get the acount value which belongs to the max value, right? If so, I think you can create a dataset which is used to retrieve acount value corresponding the max value. Then create a parameter with hiden property whose value come from this dataset, then at the footer of table, you can utilize this parameter to display the acount value 20.

If you have any question about the steps, please feel free to let us know.

• Marked as answer by Thursday, October 7, 2021 12:00 AM
Monday, July 11, 2011 10:57 PM