Display 'Top n + Other' measures using Data cube transforms
This article shows how to display top/bottom n records and the sum of the remaining records as a single value in a visualization. This can be done by using the grouping and filtering transforms in the Data cube.
2. Set Up the Data Cube
This example uses the Adventure Works database to display top 4 Product Id’s with the maximum LineTotal price and the rest of the Product Id’s LineTotal measure summed up as a single value.
Under the data connector, find the [Sales].[SalesOrderDetail] table.
Drag and drop the [Sales].[SalesOrderDetail] table on the Data cube designer canvas.
An automatic ETL process consisting of SQL Select transform and the Process Result is created.
2.1. Top/Bottom Transform
Select the connection link and insert a Top/Bottom transform.
Right click on the Top/Bottom Transform and select Configure.
In the configuration dialog, insert the number of records you want to see in the Record Count. From the Sorting Transform Input, drag the measure being sorted for the Top/bottom records (LineTotal)
2.2. Union Transform
Drag and drop another [Sales].[SalesOrderDetail] table on the Data cube designer canvas.
Connect both the SQL selects using a Union transform.
2.3. Sort Transform
Select the connection link between the second SQL select and the Union transform.
Insert a Sort Transform and right click on it to configure it. In the Configuration dialog, drag the LineTotal from the Sorting Transform Input to the Sorting elements and sort it in the Descending order.
2.4. Rank Transform
Select the connection link between the Sort Transform and the Union Transform.
Insert a Rank transform from the Insert Other components in the toolbar.
Same as before, right click on the rank transform and configure it. Drag Linetotal from Rank Transform Input to Ranking Elements.
2.5. Filter Transform
Select the connection link between Rank Transform and the Union Transform.
Insert a Filter Transform from the Insert common transforms in the toolbar. Right click to configure it.
Click on the edit icon beside the Rank element. Choose Greater than from Element Operator dropdown and set the value as 4. This will give all the records that have a rank greater than 4 i.e. all the records except the top 4 records.
2.6. Aggregate Transform
Next, click on the connection link between the Filter Transform and the Union Transform and insert the Aggregate Transform.
Uncheck all the measures or dimensions that are not needed. Set the Aggregate function for LineTotal as Sum and uncheck the ProductID.
3. Preview the Data
Select the Union transform and click on Data Preview.
You can see the ProductId’s with the top 4 LineTotals and an additional row with the summed up LineTotal of the rest of the ProductId’s.
Notice that the sum of the rest of the LineTotals has an empty string for the ProductId.
3.1. Replace the Empty String
Click on the connection link between Union transform and the Process Result node.
3.1.1. Data Conversion Transform
Insert a Data conversion transform and configure it.
Change the ProductID from Int32 to String.
3.1.2. Calculated Element Transform
Click on the connection link between the Data conversion Transform and the Process Result node.
Insert a calculated element transform. Right click on the transform and click on configure.
Uncheck ProductId and add a new calculated element.
Name this calculated element as ProductID. Change the data type to String and add the following code in the expression editor:
Submit the changes.
The final Data Cube looks like this:
Click on Process Result and preview the data from the dockable windows at the bottom.
Use this data cube on the dashboard and revisualize it to any chart type.