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,
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?
Sorry, made the function ABS(Measure) - 0,8 instead of ABS(Measure - 0,8).
This works great! Thank you very much for your help.