Identifying the top value and any value 10 less than the top value

[Anonymous]

Hi

I am trying to find the easest way to write a DAX measure to carry out the following action

I have a table with the following columns

(Name), (Age), (RPR)

John,   23,   133

Alan,    25,  144

David,  43 , 145

Philip,  47,  151

Brian,   26,  134 

Tony,    22, 126

Steven, 33, 145

I want to find the top value from column (RPR) and then return True or False of the Value in (RPR) if it is the top value or the top value less 8, so the table should look like the following (any value from 143 to 151 should output 'True' any other value 'False'

(Name), (Age), (RPR), (Top or 8 less)

John,   23,   133, False

Alan,    25,  144, True

David,  43 , 145, True

Philip,  47,  151, True

Brian,   26,  134, False

Tony,    22, 126, False

Steven, 33, 145, True

I then once have the 'True or 'False statement want to turn those True & False statements into integers True=8 and false=0 and display the table as below

(Name), (Age), (RPR), (Top or 8 less) (Score)

John,   23,   133, False, 0

Alan,    25,  144, True, 8

David,  43 , 145, True, 8

Philip,  47,  151, True, 8

Brian,   26,  134, False, 0

Tony,    22, 126, False, 0

Steven, 33, 145, True, 8

Is there any one who knows the simplest way to do this?

Thanks for your time

[Anonymous · Accepted solution]

Thanks for your response, in my table that doesnt work for some reason, im getting all 0's

so I took out the -8 ) *8 and the result reads 'false' in every row, hence all the zeros

If I can just get the correct 'true' or 'false' statement first I can convert that to integers and multiply by 8 quite easily.

I was looking for a DAX method via a measure rather than a calculated column.

Thanks for you time.

[Anonymous · Accepted solution]

Apologies ! Your solution does work, there was an error in my table,

Thanks

[CNENFRNL]

Hi, @Anonymous , you might want to try this formula in a calculated column,

Score = ( Table1[RPR] >= MAX ( Table1[RPR] ) - 8 ) * 8

[Anonymous · Accepted solution]

Thanks for your response, in my table that doesnt work for some reason, im getting all 0's

so I took out the -8 ) *8 and the result reads 'false' in every row, hence all the zeros

If I can just get the correct 'true' or 'false' statement first I can convert that to integers and multiply by 8 quite easily.

I was looking for a DAX method via a measure rather than a calculated column.

Thanks for you time.