Calculating the difference between a value and the max value

[jaguinaga]

I am trying to take the difference in price between any of the prices versus the maximum value. I also want the max value to become the next highest value if I were to filter of the current max value. Any recommendations on how to accomplish this? Guessing I need a measure but I am not sure how to create something to accomplish all this. Thanks in advance!

Ex: Current different in price between the bus and the car is two. However if I filtered off the car, house would become the next highest value shifting the difference in price to 1 between the bus and the house.

 

[johnny51981]

Change your (MAX($B$2:$B$20)) to (SUBTOTAL(104,$B$2:$B$2000))

As you type out the SUBTOTAL function in your formula, you will see that a list will appear giving you different options of SUM, MIN,MAX, etc...and they are listed twice, these are the Function Numbers. 1 or 2 digit Function Numbers will result in full data set. 3 digit Function Numbers will only give results from visible data...including those that are filtered out.

 

[jaguinaga]

I am trying to take the difference in price between any of the prices versus the maximum value. I also want the max value to become the next highest value if I were to filter of the current max value. Any recommendations on how to accomplish this? Guessing I need a measure but I am not sure how to create something to accomplish all this. Thanks in advance!

Ex: Current different in price between the bus and the car is two. However if I filtered off the car, house would become the next highest value shifting the difference in price to 1 between the bus and the house.



View attachment 59828

I am trying to build this concept in power bi, Sorry for the confusion