Forum Discussion
Search for closest value
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:
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:
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?
wrote:
Any idea for this?Use the ABS function as originally suggested?