Forum Discussion
Column with maximum amount per category
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
3 Replies
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 )
Genius thank you!
