Search for closest value

[JarnoVisser]

Hello,

I have a sheet with customers, sales and cumulative sales in % (sorted customers descending by sales). I want to make a measure which searches the closest value of sales for a given %. Let's say, I want the sales for the customer which **bleep**% of sales is closest to 50%.

Any idea?

Regards,

Jarno

[v-yulgu-msft]

Hi @JarnoVisser,

Could you please post sample data and show us your desired output with an image so that I can test for you? You said " I want to make a measure which searches the closest value of sales for a given %", is this "given %" hardcoded value or dynamic value depend on user's selection?

Regards,

Yuliana Gu

[JarnoVisser]

Hi,

It is a hardcoded value. Sample data is like:

Customer %      Sales

78,7%                    500

79,3%                    1000

79,9%                    750

80,5%                    600

81,3%                     450

Now I want to find the sales for the customer % which is closest to 80%. In this case 750.

[jthomson · Accepted solution]

Make a calculated column like I suggested (for 80% you'd need 0.8 rather than 0.5) called difference, then make a measure like the below:

[JarnoVisser]

Thank you Thomson! There's only one thing left, in the column 'Difference' are also negative values like -0,28. So with the expression "MIN" he takes not the value closest to zero but the lowest value (which is -0,28).

-0,28

-0,13

-0,1

-0,5

-0,01

0,1

0,14

etc.

Any idea for this?

[jthomson]

---

wrote:

Any idea for this?

---

Use the ABS function as originally suggested?

[JarnoVisser]

Sorry, made the function ABS(Measure) - 0,8 instead of ABS(Measure - 0,8). 

This works great! Thank you very much for your help.

[jthomson]

So you've got some measure, say, JarnosMeasure, and you want to find a result for this which is closest to 50%? Not a function I've used before so the syntax may be off, but something like Nearest = ABS([JarnosMeasure]-0.5) and then look to find the smallest value for this?

[JarnoVisser]

Hello,

I have a sheet with customers, sales per customer and cumulative sales in % per customer (sorted descending). Now I want to make a measure that finds the sales for the customer which cumulative sales is closest to a fixed value, say 50%.

Any idea?

Regards,

Jarno