MDX TopSum Function

In Multidimensional Expression, TopSum function will sort the given data in the descending order and then selects the required number of records from the sorted data whose total is at least equal to specified Value.

MDX TopSum Function Syntax

The basic syntax of the MDX TopSum is:

TOPSUM (Set_Expression, Value, Numeric_Expression)

Set_Expression: Any Multidimensional Expression or attributes on which you want check.
Value: Please provide the Value. TOPSUM function will retrieve the records whose total is at least this Value. 

Numeric_Expression: Any Multidimensional Expression or measure. The TopSum function will use this Numeric_Expression to sort the data in descending order and then picks the number of records required to match the Value.

In this article we will show you, How to write TopSum function to extract the States from Geography Dimension (present in the Adventure Works Cube) whose total is at least equal to the user given value. For this, we are going to use below shown data.

MDX TopSum Function Example

The following query will return all the states present in the Geography Dimension who are contributing Sale of 40000000 of total Reseller sales Amount.


CODE


SELECT {[Measures].[Reseller Sales Amount]} ON COLUMNS,
TOPSUM (
[Geography].[Geography].[State-Province], 40000000
,[Measures].[Reseller Sales Amount]
) ON ROWS
FROM [Adventure Works]

In the above MDX Query, We selected the [Reseller Sales Amount] Measure on the Columns and [State Province] column present in the geography Dimension on Rows. Next, TopSum function will sort the data in the descending order using Measures.[Reseller Sales Amount] and then add the Sales amount of each state until it reaches 40000000.


OUTPUT




If you observe the above screenshot, If you total the Sales amount of above displayed states they approximately equal to 40243296.5. It means greater than what we required.
MDX TopSum Function Example 2


In this example, We are going to use two measures ([Measures].[Reseller Sales Amount] [Measures].[Reseller Gross Profit]).


CODE


SELECT {[Measures].[Reseller Sales Amount],
[Measures].[Reseller Gross Profit]} ON COLUMNS,
TOPSUM (
[Geography].[Geography].[State-Province], 500000
,[Measures].[Reseller Gross Profit]
) ON ROWS
FROM [Adventure Works]

In the above MDX Query, We selected two Measures ([Reseller Sales Amount], [Reseller Gross Profit]) on the Columns and [State Province] attribute present in the geography Dimension on Rows. Next, TopSum function will sort the data in the descending order using Measures.[Reseller Gross Profit] and then add the Sales amount of each state until it reaches 500000.


OUTPUT





If you observe the above screenshot, We are getting the top states whose [Reseller Gross Profit] total is 500000.

NOTE: Please use the appropriate Measure as a third argument in the TOPSUM function otherwise, You will end up with wrong results.

MDX TopPercent Function

In Multidimensional Expression, TopPercent function will sort the given data in the descending order and then selects the required number of records from the sorted data whose total is greater than or equal to specified Percentage.

MDX TopPercent Function Syntax

The basic syntax of the MDX TopPercent is:

TOPPERCENT (Set_Expression, Percentage, Numeric_Expression)

Set_Expression: Any Multidimensional Expression or attributes on which you want check.
Percentage: Please provide the percentage to want to retrieve.
Numeric_Expression: Any Multidimensional Expression or measure. The TopPercent function will use this Numeric_Expression to sort the data in descending order and then picks the number of records required to match the percentage.

In this article we will show you, How to write TopPercent function to extract the States from Geography Dimension (present in the Adventure Works Cube) who has contributed 60 percent of total sales in Reseller Sales amount. For this, we are going to use below shown data.

MDX TopPercent Function Example

The following query will return all the states present in the Geography Dimension who are contributing 60 Percent of total Reseller sales Amount.


CODE


SELECT {[Measures].[Reseller Sales Amount]} ON COLUMNS,
TOPPERCENT (
[Geography].[Geography].[State-Province], 60
,[Measures].[Reseller Sales Amount]
) ON ROWS
FROM [Adventure Works]

In the above MDX Query, We selected the [Reseller Sales Amount] Measure on the Columns and [State Province] column present in the geography Dimension on Rows. Next, TopPercent function will sort the data in the descending order using Measures.[Reseller Sales Amount] and then find the percentage of sales from total sales.


OUTPUT




If you observe the above screenshot, they are not the 60 Percent rows of Total records. If you total the Reseller Sales Amounts present in the above screenshot, it will be greater than or equal to 60 percent of the Total reseller Sales Amount. In short, above mentioned states are contributing 60 Percent Sales of total sales.
MDX Topcount Function Example 2


In this example, We are going to use two measures ([Measures].[Reseller Sales Amount] [Measures].[Reseller Gross Profit]).


CODE


SELECT {[Measures].[Reseller Sales Amount],
[Measures].[Reseller Gross Profit]} ON COLUMNS,
TOPPERCENT (
[Geography].[Geography].[State-Province], 60
,[Measures].[Reseller Sales Amount]
) ON ROWS
FROM [Adventure Works]


OUTPUT





If you observe the above screenshot, We are getting uneven results for [Reseller Gross Profit]. This is because data is sorted according to [Reseller Sales Amount]


NOTE: Please use the appropriate Measure as a third argument in the TOPPERCENT function otherwise, You will end up with wrong results.

MDX TopCount Function

In Multidimensional Expression, Topcount function will sort the given data in the descending order and then selects the required number of records from the sorted data.

For example, If you want to find the Top 10 performing products or you want to find the top 10 regions with highest sales or to calculate Highest Salary we can use this Topcount function with measure.

Similarities between MDX TOPCOUNT and SQL TOP Clause

MDX TOPCOUNT is similar to SQL TOP Clause.

Like Top clause, Topcount extract the required number of records in the order they initially stored.
To extract Top 10 performing records we have to use Order BY Clause in SQL.
To extract Top 10 performing records we have to add third argument with appropriate Measure or Numeric Value.

MDX TopCount Function Syntax


The basic syntax of the MDX Topcount is:

TOPCOUNT (Set_Expression, Count, Numeric_Expression)

Set_Expression: Any Multidimensional Expression or attributes on which you want check.
Count: Please provide, How many number of records you want to retrieve.
Numeric_Expression: Any Multidimensional Expression or measure. This is purely optional argument. If we used this argument then TOPCOUNT will sort the data in descending order using this Numeric_Expression and then picks the number of records you asked for otherwise, TOPCOUNT will select the required number of records without sorting.

In this article we will show you, How to write TopCount function to extract Top 7 States from Geography table (present in the Adventure Works Cube) whose Reseller Sales amount is greater than others with example. For this, we are going to use below shown data.

MDX Topcount Function with Two arguments

In this example we will show you, What happen when we miss the third argument in the Topcount function. The following query will return first seven records from the above source in the order they appear above.


CODE


SELECT {[Measures].[Reseller Sales Amount]} ON COLUMNS,
TOPCOUNT (
[Geography].[Geography].[State-Province]
,7
) ON ROWS
FROM [Adventure Works]


In the above MDX Query, We selected the [Reseller Sales Amount] Measure on the Columns and [State Province] column present in the geography table on Rows. Next, Topcount function will select the Top 7 records irrespective of their measure values.


OUTPUT




No one will accept the above result as top 7 performing states around the world.
MDX Topcount Function Example


In this example we will show you, What happen when we add the third argument in the Topcount function. The following query will sort the records in the descending order using [Measures].[Reseller Sales Amount] and then return first seven records from the sorted data. In short, We will get our top 7 performing states around the world.


CODE


SELECT {[Measures].[Reseller Sales Amount]} ON COLUMNS,
TOPCOUNT (
[Geography].[Geography].[State-Province]
,7
,[Measures].[Reseller Sales Amount]
) ON ROWS
FROM [Adventure Works]


OUTPUT

Using Multiple Measures in MDX Topcount Function

In this example we will show you, What happen when we add wrong measure as the third argument in the Topcount function. We are going to use two measures ([Measures].[Reseller Sales Amount], [Measures].[Reseller Gross Profit]) and we assign the [Measures].[Reseller Gross Profit] as sorting argument. The following query will sort the records in the descending order using [Measures].[Reseller Gross Profit] and then return first seven records from the sorted data.


CODE


SELECT {[Measures].[Reseller Sales Amount],
[Measures].[Reseller Gross Profit]} ON COLUMNS,
TOPCOUNT (
[Geography].[Geography].[State-Province]
,7
,[Measures].[Reseller Gross Profit]
) ON ROWS
FROM [Adventure Works]


OUTPUT





Although we are expecting Top 7 Sales, above query is retrieving Top 7 States Whose Gross Profit is Highest among the others.


NOTE: Please use the appropriate Measure as a third argument in the TOPCOUNT function otherwise, You will end up with wrong results.

MDX Tail Function

In Multidimensional Expression, Tail Function is used to select the required number of records from bottom of the source data.

For example, If you want to find the Bottom 10 performing products or  you want to find the Bottom 10 regions with Lowest sales or to calculate Lowest Salary we can use this Head function along with MDX Order Function.

MDX Tail Function Syntax

MDX Tail Function allows only two arguments. The basic syntax of this function is:

1	TAIL (Set_Expression, Count)

• Set_Expression: Any Multidimensional Expression or attributes on which you want check.
• Count: Please provide, How many number of records you want to retrieve.

In this article we will show you, How to write TAIL Function to extract Bottom 7 States from Geography dimension (present in the Adventure Works Cube) whose Reseller Sales amount is lower than others with example. For this, we are going to use below shown data.

MDX Tail Function Example

In this example we will show you, How to write the Tail function. The following query will return Last seven records from the above source in the order they appear above.

CODE

1 2 3 4 5 6

SELECT {[Measures].[Reseller Sales Amount]} ON COLUMNS, TAIL ( [Geography].[Geography].[State-Province]         ,7        ) ON ROWS FROM [Adventure Works]

In the above MDX Query, We selected the [Reseller Sales Amount] Measure on the Columns and [State Province] attribute present in the geography Dimension on Rows. Next, Tail function will select the Bottom 7 records irrespective of their measure values.

OUTPUT

No one will accept the above result as Least performing states around the world. They we just Bottom 7 records.

Using Order Function along with MDX Tail Function

In this example we will show you, What happen when we add the MDX Order Function.

CODE

1 2 3 4 5 6

SELECT {[Measures].[Reseller Sales Amount]} ON COLUMNS, ORDER (     TAIL ([Geography].[Geography].[State-Province], 7)--Already Picked Data    ,[Measures].[Reseller Sales Amount], DESC        ) ON ROWS FROM [Adventure Works]

OUTPUT

Analysis:

First Tail function will select the Last 7 records present in the source data using below statement

1	TAIL ([Geography].[Geography].[State-Province], 7)

Next, Order function outside the Tail function will sort the already picked data in the descending order using their Reseller Sales Amount.

1 2 3 4

ORDER (     TAIL ([Geography].[Geography].[State-Province], 7)    ,[Measures].[Reseller Sales Amount], DESC        ) ON ROWS

It means, We are getting Last 7 records from the source data in Descending order

Let us change the above code:

CODE

1 2 3 4 5 6 7 8 9 10

SELECT {[Measures].[Reseller Sales Amount]} ON COLUMNS,   TAIL (     ORDER (           [Geography].[Geography].[State-Province]          ,[Measures].[Reseller Sales Amount]          ,BDESC            )      ,7       ) ON ROWS FROM [Adventure Works]

OUTPUT

Analysis

First, Order function will sort the State Province data in the Descending order using their Reseller Sales Amount. We used BDESC to break the hierarchy while sorting the data.

1 2 3 4 5

ORDER (         [Geography].[Geography].[State-Province]        ,[Measures].[Reseller Sales Amount]        ,BDESC       )

Next, Tail function will pick the Last 7 records from the already sorted data.

1 2 3 4 5 6 7 8

TAIL (     ORDER (           [Geography].[Geography].[State-Province]          ,[Measures].[Reseller Sales Amount]          ,BDESC            )      ,7       ) ON ROWS

Now, We got our Bottom 7 records or Least performing states.

NOTE: Please use the appropriate Measure as second argument in the ORDER function otherwise, You will end up with wrong results.

Using NON EMPTY in MDX Tail Function

In this example we are going to use Non Empty keyword to remove the NULL values.

CODE

1 2 3 4 5 6 7 8 9 10 11

SELECT {[Measures].[Reseller Sales Amount]} ON COLUMNS, NON EMPTY   TAIL (     ORDER (           [Geography].[Geography].[State-Province]          ,[Measures].[Reseller Sales Amount]          ,BDESC            )      ,7       ) ON ROWS FROM [Adventure Works]

OUTPUT

If you observe the above screenshot, MDX Non Empty keyword we used in the above query removed those Null records. Now, it looks Nice and Easy 

Thank You for Visiting Our Blog