PDA

View Full Version : Median



irishbread
2016-11-11, 10:36 PM
It looks like PQ ignores/removes duplicates to calc median. Is this a bug? Thanks.

The "median calc" is per excel. "PQ result" is using Group By (contents in column A = "ABC"; contents in column B are values per below).



654.5150

654.5150


654.5300

654.5300


654.7650

654.7650


654.7950

654.7950


654.6750

654.6750


654.6600

654.6600


654.7200

654.7200


654.7200














654.6975

654.6750


median calc

PQ result

Comfy
2016-11-14, 09:32 AM
It looks like PQ ignores/removes duplicates to calc median. Is this a bug? Thanks.

The "median calc" is per excel. "PQ result" is using Group By (contents in column A = "ABC"; contents in column B are values per below).



654.5150

654.5150


654.5300

654.5300


654.7650

654.7650


654.7950

654.7950


654.6750

654.6750


654.6600

654.6600


654.7200

654.7200


654.7200














654.6975

654.6750


median calc

PQ result




I get a Median of 654.6750 in Excel. What Function are you using? Does it reference the entire range?

Bill Szysz
2016-11-14, 10:55 AM
Hi irishbread :-)
If number of items on list is odd, then median in PQ is exactly the same as in excel.
But if number of items is even, then median in PQ is different but mathematically correct.(read description of function List.Median).
If you want to get the same median like in excel, you should write something like this below.

= if Number.IsOdd(List.Count(YourList)) then List.Median(YourList) else List.Sum(List.Range(List.Sort(YourList), List.Count(YourList)/2-1,2))/2
where "YourList" is your data (as a list).

Regards

irishbread
2016-11-15, 03:26 PM
Thanks Bill. This is helpful.