Median Not Working correctly?

[mrmmickle1]

I was able to create this Dax Forumula with some help from a Marco Russo SQLBI blog post:

=(    MINX (
        FILTER (
            VALUES ( Data[Salary] ),
            CALCULATE (
                COUNT ( Data[Salary] ),
                CLT_Data[Salary]
                    <= EARLIER ( Data[Salary] )
            )
                > COUNT ( Data[Salary] ) / 2
        ),
        Data[Salary]
    )
        + MINX (
            FILTER (
                VALUES ( Data[Salary] ),
                CALCULATE (
                    COUNT ( Data[Salary] ),
                    Data[Salary]
                        <= EARLIER ( Data[Salary] )
                )
                    > ( COUNT ( Data[Salary] ) - 1 ) / 2
            ),
            Data[Salary]
        )


) / 2

It looks to be off by a little bit....

When I sort my 467 Salary values in Ascending Order I have

Data like this:

Excel 2010
	A
233	42500
234	42500
235	43000
Sheet1

Excel Median Function returns 42,500. DAX Formula Returns 43,000.

Shouldn't the answer be 42,500?

 

[jorismoerings]

Hi,
Yes as the median is the number in the middle of a set of numbers. The middle in a set of 467 is number 234.

 

[mrmmickle1]

I realize the answer is wrong(Note: I do not have headers if that's what you were thinking). Does anyone have any input on how to correct the issue?

 

[Ozeroth]

I can't seem to replicate your issue in either Excel 2010 or 2016.

If I create a list of 467 numbers where the 234th is 42,500, the SQLBI formula gives me the correct Median = 42,500.

Also, is the CLT_Data[Salary] a typo on line 6 of your DAX above?

Can you post a sample workbook?

 

[mrmmickle1]

You are correct that was a typo. Yeah...i think its odd. I don't get it.

 

[cmcreynolds]

Just my two cents - do you have any blank data for that field?