Archive
Displaying Top N, Bottom N, and “All Others” – Dynamically
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
The base data looks like the below:
Both table as added to Model as the linked table.
I created the following calculated measure in the Power Pivot Model:
SelectedN:=MAX([N])
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
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: https://onedrive.live.com/?cid=8BA997FE18F7FDAE&id=8BA997FE18F7FDAE%212373
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 (
Count(‘Date'[Date])=1,
( 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
