Archive for the ‘BISM Tabular’ Category

Displaying Top N, Bottom N, and “All Others” – Dynamically

November 12, 2014 12 comments

I just read Rob Collie’s post Displaying Top N, Bottom N, and “All Others”. Its a great post with lots of details.

I’ve come up with another approach that use the Set to achieve the similar outcome but dynamically. The final solution looks like the below

Dynamic TopN and AllOthers

The base data looks like the below:

base data

Both table as added to Model as the linked table.

I created the following calculated measure in the Power Pivot Model:


Total Sales:=SUM(Sales[Sales])

Rank:=RANKX(All(Sales),[Total Sales])

Top N Total Sales:=CALCULATE([Total Sales],FILTER(Sales,[Rank]<=[SelectedN]))

All Other Total Sales:=CALCULATE([Total Sales],FILTER(Sales,[Rank]>[SelectedN]))

In addition to the DAX fomula, I created two sets using MDX

Excel OLAP Set

Set Name: TopN

Set Definition: TOPCOUNT([Sales].[CustomerID].Children, [Measures].[SelectedN], [Measures].[Sum of Sales])

Set Name: AllOthers

Set Definition: [Sales].[CustomerID].Children – [TopN]

* Both of the above two sets has the checkbox “Recalculate set with every update” checked.

You can download the workbook (Excel 2013 version) at:

Categories: BI, BISM Tabular, DAX, Excel, PowerPivot Tags: ,

Custom Rollup in BISM tabular (PowerPivot)

As we know, the BISM tabular does not support custom rollup. However, I recently read the article here and found it is possible to achieve custom aggregation via DAX. It is not exactly the same as custom rollup with unary operator in MDX. But it at least can perform different calculation at different hierarchy level. Basically, you need nested Summarize statement to specify the table (calcuated, returned by DAX) to loop with. The drawback is that you have to maintain the DAX.

I wonder is it possible to add a few columns to the date dimension to specify the calculation logic for different level. For example, ‘DimDate'[YearCalculationUnaryOperator], ‘DimDate'[QtrCalculationUnaryOperator], ‘DimDate'[MonthCalculationUnaryOperator], ‘DimDate'[DayCalculationUnaryOperator]. And in the measure, use the DAX looks like:

if (
(    Switch(‘DimDate'[DayCalculationUnaryOperator],
‘+’, <Calculation expression for +>,
‘-‘, <Calculation expression for ->,
‘/’, <Calculation expression for />,
<default Calculation expression >)
if    (    Count(‘Date'[Month])=1,
(    Switch(‘DimDate'[MonthCalculationUnaryOperator],
‘+’, <Calculation expression for +>,
‘-‘, <Calculation expression for ->,
‘/’, <Calculation expression for />,
<default Calculation expression >)
, <default Calculation expression for other hierarchy>

Well, time to dig into the BISM