SQL Server: Toppercent and Bottompercent in MDX

            In my previous blog post “Top to Bottom in MDX” we have shown Topcount and Bottomcount functions. (I suggest you to first go throgh this post to get more clear idea about the current topic.) The Topcount function returns the top N tuples of a set and the Bottomcount function returns the bottom N tuples. But there can be a possibility that the top 5 Resellers may constitue only a small percent of the total Reseller Sales. Using the Toppercent / Bottompercent functions we can directly get a proportional threshold.

Toppercent

            It returns the largest vallues required to hit the defined percentage by sorting the data in descending order. Let’s continue with the same query in the previous post by just replacing the topcount with toppercent.

select 
	[Measures].[Reseller Sales Amount] on columns,
	toppercent
	(
		[Reseller].[Reseller].[Reseller], --Set_Expression
		10, --Percent 
		[Measures].[Reseller Sales Amount] --Numeric_Expression
	) on rows
from 
[Adventure Works]

          The query has returned twelve resellers who constitue 10 percent of the total Resellers Sale. If you remember, the first five resellers are the same returned by the topcount query in the previous post as the query sorts the resellers from higher to lower Sales. So the Toppercent adds rows till the percent specified is reached (In this case 10).

Bottompercent

            As Opposed to Toppercent, is the Bototmpercent fuction which behaves exactly opposite to Toppercent. It returns the smallest vallues required to hit the defined percentage by sorting the data in ascending order.

select 
	[Measures].[Reseller Sales Amount] on columns,
	Nonempty (bottompercent
	(
		[Reseller].[Reseller].[Reseller], --Set_Expression
		5, --Percent 
		[Measures].[Reseller Sales Amount] --Numeric_Expression
	)) on rows
from 
[Adventure Works]

          I have just added the nonempty function here to ignore the Resellers with no sales. The query is showing a part of the result and has returned those Resellers lowest Sales Amount and contributes only five percent of total Reseller Sales.

          Simple, isn’t it? Now I hope you must be finding MDX as simple as SQLJ. In the next post I will be showing the next two in the series; TopSum and Bottomsum.