Forum Discussion
Search for closest value
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
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:
8 Replies
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
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?
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
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.
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:
