I need some help on computing a table calculation in the right way.
I have two dimensions (chosen by the user through a parameter): LEVEL 1 and SPLIT. For example, LEVEL 1 can be = CLIENTS and SPLIT can be = EXCHANGE.
I would like to display a table displaying each LEVEL 1 element (e.g., each client), its top 10 SPLIT (e.g., exchanges), with top defined by the measure [T1 LOTS - GIVE IN] and all the other SPLIT elements grouped as "OTHERS".
This is the best I was able to get:
I defined the calculated field Index (INDEX()) that ranks each SPLIT by addressing LEVEL 1 and SPLIT and sorts by T1 LOTS - GIVE IN:
"In top 10?" is a boolean calculated field defined as:
[Index] <= 10
and with the same Table Calculation configuration as Index.
The calculated field [SPLIT with OTHERS grouped (CLIENT OR EXCHANGE )] is defined as:
|IF [In top 10?]|
The measure displayed is a calculated field [T1 WINDOW SUM LOTS - GIVE IN] defined as:
WINDOW_SUM(SUM([T1 LOTS - GIVE IN]))
The Table Calculation for [T1 WINDOW SUM LOTS - GIVE IN] is "cell".
The last part that I can't have working with the rest are:
1) "grouping" the OTHERS, i.e., all SPLIT = OTHERS should be grouped together and the measure should be summed. For example, for the client in the image below I would like to see one row for "OTHERS" and the corresponding measure = 3272 (the sum of the single values for all the OTHERS of that client). The columns that I will display will be: LEVEL 1, [SPLIT with OTHERS grouped (CLIENT OR EXCHANGE )] and [T1 WINDOW SUM LOTS - GIVE IN].
2) Display only NOT EMPTY rows. I tried dropping the [T1 WINDOW SUM LOTS - GIVE IN] measure to the filter shelf and select special>non-null values but the empty rows didn't disappear.
3) make sure the sorting works fine. the sorting should be:
LEVEL 1 (by [T1 WINDOW SUM LOTS - GIVE IN])
For each LEVEL 1, the rows should be sorted by [T1 WINDOW SUM LOTS - GIVE IN] with OTHERS always at the end
Many thanks in advance for any help.