Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Let's say I have a pivot table with subtotal like this (assume Expr in this example is a simple Sum, subtotals in italics):
Dim1 | Dim2 | Dim3 | Expr |
A | 14 | ||
A | X | 7 | |
A | X | 1 | 3 |
A | X | 2 | 4 |
A | X | 8 | |
A | Y | 3 | 6 |
A | Y | 4 | 2 |
B | 16 | ||
B | X | 10 | |
B | X | 1 | 7 |
B | X | 2 | 3 |
B | X | 6 | |
B | Y | 3 | 1 |
B | Y | 4 | 5 |
If I want to show first value of each group at the lowest grain level as the subtotal, what would be the best way to achieve this, preferably without having to touch the data model? I'm trying various combinations of Aggr() and inter-record functions (e.g. Top() ), but so far nothing works fully.
For reference, the final pivot table should look like this:
Dim1 | Dim2 | Dim3 | Expr |
A | 3 | ||
A | X | 3 | |
A | X | 1 | 3 |
A | X | 2 | 4 |
A | X | 6 | |
A | Y | 3 | 6 |
A | Y | 4 | 2 |
B | 7 | ||
B | X | 7 | |
B | X | 1 | 7 |
B | X | 2 | 3 |
B | X | 1 | |
B | Y | 3 | 1 |
B | Y | 4 | 5 |
Any input will be greatly appreciated.
Nevermind I think I've got it
For the record, formula I was looking for is this:
FirstSortedValue( Aggr( Sum(Expr), (Dim1,(TEXT, ASCENDING)), (Dim2, (TEXT, ASCENDING)), (Dim3, (NUMERIC, ASCENDING)) ), Aggr( RowNo(TOTAL), (Dim1,(TEXT, ASCENDING)), (Dim2, (TEXT, ASCENDING)), (Dim3, (NUMERIC, ASCENDING)) ) )
This assumes sort order in the table will remain constant, but I can live with that.
You would probably need to use Dimensionality here. May be try playing around with it a little, else share a sample where you are trying to use this and we should be able to help you better
Nevermind I think I've got it
For the record, formula I was looking for is this:
FirstSortedValue( Aggr( Sum(Expr), (Dim1,(TEXT, ASCENDING)), (Dim2, (TEXT, ASCENDING)), (Dim3, (NUMERIC, ASCENDING)) ), Aggr( RowNo(TOTAL), (Dim1,(TEXT, ASCENDING)), (Dim2, (TEXT, ASCENDING)), (Dim3, (NUMERIC, ASCENDING)) ) )
This assumes sort order in the table will remain constant, but I can live with that.