Median PowerBI vs Excel

[ace_SB]

Hi All,

I am using Median function for some fields. For two fields, the median value in excel is different than what I get in PowerBI. The one is excel is what I want. Seems like excel is ignoring null values while PowerBI is not. Do I need to change the way I use Median function in PowerBI to get the results that I get in excel?

Thanks

[v-piga-msft]

Hi @ace_SB，

You could have a reference of this similar thread.

Best Regards,

Cherry

[ace_SB]

Thanks Cherry.

How can we ignore NULL values in the median function?

This is the formula I am using right now:

MedScore = ROUND(CALCULATE(medianx(vw_ABC,(vw_ABC[Score1])),
TREATAS(VALUES('vw_Option'[StuName]),vw_ABC[StuName])),0)

[ace_SB · Accepted solution]

I think I got it:

MedScore = ROUND(
CALCULATE(
   medianx(FILTER(vw_ABC, vw_ABC[Score1]<> BLANK()),

              vw_ABC[Score1]) ,

           TREATAS(VALUES('vw_Option'[StuName]),vw_ABC[IStuName])
),0
)

[ace_SB]

Any suggestion?