REQUEST DEMO
Contact us
en
en
Community
Library
Blog
Training
Documentation
Forum
Feedback
Portal
Welcome Guest! To enable all features please Login or Register.
Calculate Median of a Data Set
Options
Go to last post Go to first unread
Previous Topic Next Topic
Chris Richard  
#1 Posted : Friday, January 10, 2014 4:28:20 PM(UTC)
Chris Richard
Rank: Newbie

Groups: extranet\Forum
Joined: 12/18/2013(UTC)
Posts: 4
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.

Thanks,

Chris
Back to top
PM
Niels Thomsen  
#2 Posted : Wednesday, January 15, 2014 6:30:14 PM(UTC)
Niels Thomsen
Rank: Advanced Member

Groups: extranet\Forum
Joined: 4/5/2011(UTC)
Posts: 110
Hi Chris

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 have created an example here:

1. I have a crosstab showing the sales results of a number of sales guys – the numbers are sorted ascendingly
UserPostedImage

2. I want to find the middle observation – median – so I do a calculated column with a count of all the dimension members from the top
count(d1,d1:0,m1)
– this gives me a column with numbers to identify each observation (sales result)
UserPostedImage

3. The median is the middle observation – but if the numbers of observations is even – the median is actually the mean of the 2 middle observations – so I have to find out if I have an even or odd number of observations – formula:
mod(max(c1, all, m1);2)
- this return 1 for odd numbers and 0 for even
UserPostedImage

4. I need to find the middle observation or middle 2 observations depending on whether I have an odd or even number of observation – new calculation:
if sum(c2, 0, m1) = 1 then (max(c1, all, m1) + 1) / 2 else (max(c1, all, m1) + 2) / 2
– this will get number of the median observation. I f the number of observations is even – it will get the second of the 2 middle observations
UserPostedImage

5. Now to calculate the median for an odd number of observations – yet another calculation:
if count(d1, d1:0, m1) = sum(c3, 0, m1) then sum(d1, 0, m1) else 0
In this case however this is not true since we have an even number of observations – so…
UserPostedImage

6. …if there is an even number of observations:
if count(d1, d1:0, m1) = sum(c3, 0, m1) then (sum(d1, 0, m1) + sum(d1, -1, m1)) / 2 else 0
This takes the mean of the observation I counted my way to plus the previous observation
UserPostedImage

7. Finally I’m home – I just need one number which is the median – so depending on whether we have an odd or even number of observations:
if sum(c2, 0, m1) = 0 then sum(c5, 0, m1) else sum(c4, 0, m1)

Hiding all the intermediates – it will end up like this:
UserPostedImage

If someone on the forum has an easier way to get there - don't hesitate to post it :-)

Hope it makes sense.


Edited by user Wednesday, January 15, 2014 6:47:20 PM(UTC)  | Reason: Not specified

Hope it makes sense.

Best Regards
Niels Thomsen

https://dk.linkedin.com/in/ncthomsen


Back to top
PM
Páll Bjarkason  
#3 Posted : Wednesday, April 13, 2016 4:03:37 PM(UTC)
Páll Bjarkason
Rank: Advanced Member

Groups: extranet\Forum
Joined: 1/13/2012(UTC)
Posts: 183
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.


UserPostedImage


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

Best regards

Páll, CTS
Back to top
PM
Ole  
#4 Posted : Thursday, September 14, 2017 1:36:03 PM(UTC)
Ole
Rank: Administration

Groups: extranet\Forum
Joined: 4/7/2011(UTC)
Posts: 497
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.

My solution require three calculations:

Median Low: if allcount(d1, d1:0, m1) = floor((allcount(d1, all, m1) / 2 + 0,5)) then sum(d1, 0, m1) else 0
Median High: if allcount(d1, d1:0, m1) = ceil((allcount(d1, all, m1) / 2 + 0,5)) then sum(d1, 0, m1) else 0
Median: (sum(c1, all, m1) + sum(c2, all, m1)) / 2

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.

UserPostedImage
Ole Dyring
Back to top
PM
Users browsing this topic
Anonymous
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.

Powered by YAF | YAF © 2003-2019, Yet Another Forum.NET

Notification

Icon
Error

We use cookies to improve your site experience, but they also provide us with information on your use of our website.
To find out more about the cookies we use and how to delete them, see our Privacy Policy. By continuing to browse the site, you are consenting to our use of cookies.

ok