Altering the default calculation order in a RunningSum
today there was a discussion about changing the order in which RunningSum is calculated in a table (RunningSum() sorted by Classification()).
This is the data presented. The RunningSum should be calculated in descending order by cost.
The solution I proposed was redefine the context of the calculation using the In
The secret here was to create a dummy dimension which should give the correct order to calculate it.
I create the dummy variable [cost_dim] = 0 – [cost].
If the table was ordered by [cost dim] (meaning descending order by [cost]), the result will be presented correctly, but another requirement was not to alter the table´s structure.
So, we must find a way of calculating RunningSum as if the table was sorted by [cost dim] .
As a rule of thumb, when you redefine a context, the order in which you set the dimension that redefines it will give you the default sort of the calculation.
If you set the redefinition to ([cost_dim]), the calculation will happens in ascending order of [cost_dim] (descending order of [cost], as needed).
Using RunningSum([cost]) In ([cost_dim]) will force RunningSum to be calculated as needed.