Is there a formula to calculate the Median value from a data set?
As a work-around, I have created Columns to calculate an index with the "Count" formula.
No unfortunately there is no bulit-in median function in the TARGIT client. However it is possible to find the median using the existing functions with a little work.
I am not sure whether it is easier, but it is possible to keep the dimension order when calculating the median.
In some cases an unsorted dimension is preferred, e.g. when portraying calendar months, hence the solution to the problem is slightly different.
It seems to check out that medianLow and medianHigh are rowNumber 6 and 7.
Note: Using the AlwaysOne ensures the presence of 12 moths (dimension members) in the year, and is not subject to the occurrence of (past or future) gaps in data.
In this case, the numbers of observations is always even and there is no need for the mod operator.
Read more on AlwaysOne here.
A more generic approach would be to implement a measure in MDX.
As Páll described, you can calculate the Median even on unsorted data. However, it will only work if you have a fixed number of values in your data set - as in Páll's example, you must assume that you are always working on 12 months.
If your data set is a dynamic data set - some times it is based on 7 values, some times 12 values, some times 18 values etc. - then, unfortunately, you can only make it work with the sorted approach, as described by Niels above.
With the sorted approach, I did, however, come up with a slightly modified solution that involves less calculations.
The screenshot below shows a result for an even number of values, but an uneven number of values will also be handled correctly by this solution.