Hi Niels
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.Xview code snippet:
<measureCalculations>
<formula>
<title>minNumber1</title>
<expression>min(d1, all, m1)</expression>
<color>6FB121</color>
<hidden>true</hidden>
<format reversesign="false" astime="false" abbreviatebmk="false">Standard</format>
<label visible="true" measure="false" dimension="false" percent="false" value="false" total="false"/>
</formula>
<formula>
<title>minNumber1Post</title>
<expression>if sum(d1, 0, m1) = sum(d1, 0, m2) then max(d1, all, m1) else sum(d1, 0, m1)</expression>
<color>3AAADC</color>
<hidden>true</hidden>
<format reversesign="false" astime="false" abbreviatebmk="false">Standard</format>
<label visible="true" measure="false" dimension="false" percent="false" value="false" total="false"/>
</formula>
<formula>
<title>minNumber2</title>
<expression>min(d1, all, m3)</expression>
<color>E42322</color>
<hidden>true</hidden>
<format reversesign="false" astime="false" abbreviatebmk="false">Standard</format>
<label visible="true" measure="false" dimension="false" percent="false" value="false" total="false"/>
</formula>
<formula>
<title>minNumber2Post</title>
<expression>if sum(d1, 0, m3) = sum(d1, 0, m4) then max(d1, all, m1) else sum(d1, 0, m3)</expression>
<color>019A9A</color>
<hidden>true</hidden>
<format reversesign="false" astime="false" abbreviatebmk="false">Standard</format>
<label visible="true" measure="false" dimension="false" percent="false" value="false" total="false"/>
</formula>
<formula>
<title>minNumber3</title>
<expression>min(d1, all, m5)</expression>
<color>E7511E</color>
<hidden>true</hidden>
<format reversesign="false" astime="false" abbreviatebmk="false">Standard</format>
<label visible="true" measure="false" dimension="false" percent="false" value="false" total="false"/>
</formula>
<formula>
<title>minNumber3Post</title>
<expression>if sum(d1, 0, m5) = sum(d1, 0, m6) then max(d1, all, m1) else sum(d1, 0, m5)</expression>
<color>EC80AD</color>
<hidden>true</hidden>
<format reversesign="false" astime="false" abbreviatebmk="false">Standard</format>
<label visible="true" measure="false" dimension="false" percent="false" value="false" total="false"/>
</formula>
<formula>
<title>minNumber4</title>
<expression>min(d1, all, m7)</expression>
<color>016B6A</color>
<hidden>true</hidden>
<format reversesign="false" astime="false" abbreviatebmk="false">Standard</format>
<label visible="true" measure="false" dimension="false" percent="false" value="false" total="false"/>
</formula>
<formula>
<title>minNumber4Post</title>
<expression>if sum(d1, 0, m7) = sum(d1, 0, m8) then max(d1, all, m1) else sum(d1, 0, m7)</expression>
<color>97BE0D</color>
<hidden>true</hidden>
<format reversesign="false" astime="false" abbreviatebmk="false">Standard</format>
<label visible="true" measure="false" dimension="false" percent="false" value="false" total="false"/>
</formula>
<formula>
<title>minNumber5</title>
<expression>min(d1, all, m9)</expression>
<color>F29400</color>
<hidden>true</hidden>
<format reversesign="false" astime="false" abbreviatebmk="false">Standard</format>
<label visible="true" measure="false" dimension="false" percent="false" value="false" total="false"/>
</formula>
<formula>
<title>minNumber5Post</title>
<expression>if sum(d1, 0, m9) = sum(d1, 0, m10) then max(d1, all, m1) else sum(d1, 0, m9)</expression>
<color>00487D</color>
<hidden>true</hidden>
<format reversesign="false" astime="false" abbreviatebmk="false">Standard</format>
<label visible="true" measure="false" dimension="false" percent="false" value="false" total="false"/>
</formula>
<formula>
<title>MedianLow</title>
<expression>min(d1, all, m11)</expression>
<color>F5BDD5</color>
<format reversesign="false" astime="false" abbreviatebmk="false">Standard</format>
<label visible="true" measure="false" dimension="false" percent="false" value="false" total="false"/>
<misc>
<item name="GraphType">L</item>
</misc>
</formula>
<formula>
<title>MedianLowPost</title>
<expression>if sum(d1, 0, m11) = sum(d1, 0, m12) then max(d1, all, m1) else sum(d1, 0, m11)</expression>
<color>94C36A</color>
<hidden>true</hidden>
<format reversesign="false" astime="false" abbreviatebmk="false">Standard</format>
<label visible="true" measure="false" dimension="false" percent="false" value="true" total="false"/>
</formula>
<formula>
<title>MedianHigh</title>
<expression>min(d1, all, m13)</expression>
<color>8698C8</color>
<format reversesign="false" astime="false" abbreviatebmk="false">Standard</format>
<label visible="true" measure="false" dimension="false" percent="false" value="false" total="false"/>
<misc>
<item name="GraphType">L</item>
</misc>
</formula>
</measureCalculations>
Edited by user Wednesday, April 13, 2016 4:16:38 PM(UTC)
| Reason: Not specified