SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Boyan Penev / Custom Rounding and Truncation of Numbers in MDX / Latest PostsInstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 18 Apr 2014 21:22:22 GMT20RE: Custom Rounding and Truncation of Numbers in MDXhttp://www.sqlservercentral.com/Forums/Topic798332-1420-1.aspxHeh,After SQL Server Central published the article, I found this really good reference on Custom Rounding:http://support.microsoft.com/kb/196652In the end of the page there are examples, and the SymArith function does exaclty what my rounding function does, just that it avoids a Div by 0 on a 0 number, and additionally, does not require the Excel functions to be available on the server. The actual code in MDX is:Fix([Measures].[???] * Factor + 0.5 * Sgn([Measures].[???])) / FactorWhere Factor is the rounding factor - 1 for 0 decimal places, 10 for 1 and so on (defined by 1/Factor).Of course, Factor of 0 will give us Div by 0 error and for truncation we can just use the truncation function in my article.Furthermore, Chris Webb advises that if you have the Excel libraries installed on your server, you can just use Excel!Round() instead of the function I've provided (it requires Excel to be installed, as it uses Ceiling).It is kind of bad that I did not include all this in the main article and I will ask SQL Server Central admins to include it as it is a definite miss.Tue, 06 Oct 2009 15:49:51 GMTBoyan PenevRE: Custom Rounding and Truncation of Numbers in MDXhttp://www.sqlservercentral.com/Forums/Topic798332-1420-1.aspxThere's a reason why it's called "Banker's Rounding". Summating a column of numbers that are all rounded using arithmetic rounding may introduce a rounding error that produces a higher total than summating the same column of numbers without rounding then rounding the total. Bankers rounding mitigates this rounding error. It's not wrong, it's mearly different. As long as you're aware, you can use the proper method for your application.Tue, 06 Oct 2009 10:34:45 GMTslupton1RE: Custom Rounding and Truncation of Numbers in MDXhttp://www.sqlservercentral.com/Forums/Topic798332-1420-1.aspxI tend to use standard rounding via a user function ROUNDIT(RawVal,DecPl,RoundVal) which uses a similar approach to the example:-FIX(RawVal * 10^DecPl + (SGN(RawVal) * RoundVal))/ 10 ^ DecPlwhere RawVal is the value to be rounded DecPl is the number of decimal places required RoundVal is the rounding factorThis ensures that negative values get rounded DOWN e.g. -1.5 gets rounded to -2.0:cool:(Useful where a value is contra'd out so that sum of 1.25 and -1.25 returns zero) - if this is not required then change the function to:- FIX(RawVal * 10^DecPl + RoundVal)/ 10 ^ DecPlIf rounding OFF is required (surplus dec places removed) then RoundVal should be passed as 0.Tue, 06 Oct 2009 04:05:10 GMTEsalterRE: Custom Rounding and Truncation of Numbers in MDXhttp://www.sqlservercentral.com/Forums/Topic798332-1420-1.aspxNice article Boyan, - I found it extermely interesting.Tue, 06 Oct 2009 02:48:38 GMTAdam AspinRE: Custom Rounding and Truncation of Numbers in MDXhttp://www.sqlservercentral.com/Forums/Topic798332-1420-1.aspxThis article [url=http://en.wikipedia.org/wiki/Rounding]http://en.wikipedia.org/wiki/Rounding[/url] explains various rounding techniques.After reading the Wiki article, my guess is that MDX uses it because it is the only deterministic method which is essentially non-biased, also in presence of a predominant sign among samples. Or simply because it is the .NET default.Thank you for the article, it opened my eyes to looking closely at current implementation of rounding every time I encounter a new environment or revisit a supposedly familiar one after a while. Assume nothing.Tue, 06 Oct 2009 01:49:17 GMTArto AhlstedtCustom Rounding and Truncation of Numbers in MDXhttp://www.sqlservercentral.com/Forums/Topic798332-1420-1.aspxComments posted to this topic are about the item [B]<A HREF="/articles/MDX/67928/">Custom Rounding and Truncation of Numbers in MDX</A>[/B]Tue, 06 Oct 2009 00:11:27 GMTBoyan Penev