2 Replies Latest reply on Nov 24, 2016 8:50 AM by chandra shekhar banerjee

# Calculation of median from 4 calculated measures

Hi All,

I have 4 calculated field which are count of orderids for 4 weeks

Calculation for count of order ids are mentioned below:

Calculation:1

countd(if DATEPART('week',[Order Date])=30 THEN [Order ID] END)

Calculation:2

countd(if DATEPART('week',[Order Date])=29 THEN [Order ID] END)

Calculation:3

countd(if DATEPART('week',[Order Date])=28 THEN [Order ID] END)

Calculation:4

countd(if DATEPART('week',[Order Date])=27 THEN [Order ID] END)

from this 4 aggregated values, I required to find out the mean. So if the output of above mentioned 4 calculations are 30,20,50,60 then I require median of those 4 numbers.

I tried to create pivot in datasource level whcih is not happening or I might be doing wrong somewhere.

Kindly help me to solve the issue.

Thanks,

Chandra Shekhar

• ###### 1. Re: Calculation of median from 4 calculated measures

Maybe someone will have a nice, short solution, but this gets the job done, just search and replace the value fields with your field names:

([Value 1] *

(IF (([Value 1]>[Value 2] and [Value 1]>[Value 3] and [Value 1]<[Value 4]) OR

([Value 1]>[Value 2] and [Value 1]<[Value 3] and [Value 1]>[Value 4]) OR

([Value 1]<[Value 2] and [Value 1]>[Value 3] and [Value 1]>[Value 4])) then 1 else 0 END)

+

[Value 2] *

(IF (([Value 2]>[Value 1] and [Value 2]>[Value 3] and [Value 2]<[Value 4]) OR

([Value 2]>[Value 1] and [Value 2]<[Value 3] and [Value 2]>[Value 4]) OR

([Value 2]<[Value 1] and [Value 2]>[Value 3] and [Value 2]>[Value 4])) then 1 else 0 END)

+

[Value 3] *

(IF (([Value 3]>[Value 2] and [Value 3]>[Value 1] and [Value 3]<[Value 4]) OR

([Value 3]>[Value 2] and [Value 3]<[Value 1] and [Value 3]>[Value 4]) OR

([Value 3]<[Value 2] and [Value 3]>[Value 1] and [Value 3]>[Value 4])) then 1 else 0 END)

+

[Value 4] *

(IF (([Value 4]>[Value 2] and [Value 4]>[Value 3] and [Value 4]<[Value 1]) OR

([Value 4]>[Value 2] and [Value 4]<[Value 3] and [Value 4]>[Value 1]) OR

([Value 4]<[Value 2] and [Value 4]>[Value 3] and [Value 4]>[Value 1])) then 1 else 0 END)

+

[Value 1] *

(IF (([Value 1]<[Value 2] and [Value 1]<[Value 3] and [Value 1]>[Value 4]) OR

([Value 1]<[Value 2] and [Value 1]>[Value 3] and [Value 1]<[Value 4]) OR

([Value 1]>[Value 2] and [Value 1]<[Value 3] and [Value 1]<[Value 4])) then 1 else 0 END)

+

[Value 2] *

(IF (([Value 2]<[Value 1] and [Value 2]<[Value 3] and [Value 2]>[Value 4]) OR

([Value 2]<[Value 1] and [Value 2]>[Value 3] and [Value 2]<[Value 4]) OR

([Value 2]>[Value 1] and [Value 2]<[Value 3] and [Value 2]<[Value 4])) then 1 else 0 END)

+

[Value 3] *

(IF (([Value 3]<[Value 2] and [Value 3]<[Value 1] and [Value 3]>[Value 4]) OR

([Value 3]<[Value 2] and [Value 3]>[Value 1] and [Value 3]<[Value 4]) OR

([Value 3]>[Value 2] and [Value 3]<[Value 1] and [Value 3]<[Value 4])) then 1 else 0 END)

+

[Value 4] *

(IF (([Value 4]<[Value 2] and [Value 4]<[Value 3] and [Value 4]>[Value 1]) OR

([Value 4]<[Value 2] and [Value 4]>[Value 3] and [Value 4]<[Value 1]) OR

([Value 4]>[Value 2] and [Value 4]<[Value 3] and [Value 4]<[Value 1])) then 1 else 0 END)

)/2

• ###### 2. Re: Calculation of median from 4 calculated measures

Hi Chris,

Actually meanwhile I have figure out a answer for the same.

I share my work here for your reference.

calculation5

max(max(calculation1,calculation2),max(calculation3,calculation4))

This will give the highest value from the 4 values.

Same way,

calculation6

min(min(calculation1,calculation2),min(calculation3,calculation4))

This will give the lowest value from the 4 values.

Now,

((calculation1 + calculation2 + calculation3 + calculation4) - (calculation5+calculation6))/2

this will give sum of middle two values divided by 2 which is nothing but the median.

Thanks,

Chandra Shekhar