Column with maximum amount per category

[Richard_Clipsto]

Hi,

I am struggling with a calculated column. I have one column of dates, one with properties, and one with passing rents. I want a column which gives the maximum rent achieved for each property. So this would be the same figure for all time periods for a property but would be different for each property. Then in my table I can see the passing rent for each property at a specific date and then the maximum rent achieved.

e.g. what I want to achieve

Property	Date	Rent	Max Rent
A	31/01/2024	200	250
A	29/02/2024	200	250
A	31/03/2024	250	250
B	31/01/2024	150	180
B	29/02/2024	180	180
B	31/03/2024	180	180

Thanks,

Richard

[lbendlin · Accepted solution]

[Richard_Clipsto]

Genius thank you!

[SamWiseOwl]

Hi @Richard_Clipsto 

Calculated column highest rent =

var currProp = [Property]

return

calculate(
max(table[max rent])

,table[property] = currProp )

That should create a calculated column that shows next to each property the highest rent it has ever been regardless of date.

If you wanted a measure it would be slightly different:

measure highest rent =

var currProp = selectedvalue(table[Property])

return

calculate(
max(table[max rent])

,all(table)

,table[property] = currProp )