When you create a Pivot Table based on an Analysis Services cube you are able to create sets based on an MDX expression.
In this post I will talk you through how to build a Top 10 set to order the products based on the Revenue.
The starting point here is a Pivot Table that has just been created.
In the table above I have split out the measure Revenue for each Product Sub Category. The problem is that there are too many products. Before completing any further analysis I want to create a set to show the top 10 sub categories. We can do this by amending the filter manually, however, I want to ensure any subsequent analysis gives the latest Top 10 without having to amend the filter manually.
The way to do this is by creating a calculated set using MDX. If you have not come across MDX before it is a Multi-Dimensional eXpression language that is used within Analysis Services to create queries and calculations on the information contained in the cube.
In Excel 2010 we are able to add to the calculations already present in the cube by creating our own calculations from the Excel user interface.
To do this we select the Pivot Table by clicking somewhere within it, and then on the Pivot Table Tools ribbon click on the Design tab and click on the Fields,Items,Sets icon.
On the pop-up menu select Manage Sets to display the Set Manager dialogue box. From this window select New : Create Set using MDX.
In this article I am not going to discuss how MDX works as it can be very complex. However, I will explain the calculation we are using to create the Top 10 set.
Here is the calculation :
TOPCOUNT( [Product].[Product Subcategory Name].[Product Subcategory Name].members , 10, [Measures].[Sales Amount] )
The calculation begins with the TopCount function. This function returns a set of members, as requested in the 1st section of the calculation:
[Product].[Product Subcategory Name].[Product Subcategory Name].members
In the second section we provide the number of items we want in the set.
Finally, in the third part of the calculation we stipulate the basis which will be used to determine the Top members. In this case we have a simple expression which will return the top 10 based on the Sales Amount measure.
After pressing OK the resulting set is added as a new field in the pivot table fields.
You can then add the set to the Pivot report. Each time the Pivot table is refreshed the Set will be updated based on the latest data in the cube.
A sample workbook is available here. This technique can also be used with PowerPivot reports.
I hope you enjoy this brief introduction to MDX calculations.