Reply
Member
Posts: 46
Registered: ‎04-06-2017

Median Based 3 columns values how it will work in Power BI

Hi, 

 

Excel Median function accepts more than one column as parameter, But when I tried Median function in Power BI it allows only single parameter/ single column. 

 

I want to use a Median function with below columns 

 

= Median ( [Corrected Aging Date],[End hours],[start hours])  this formula is possible in Excel but same not working in Power BI. 

All columns which I am using in the above formula are within the same table only. 

Thanks and Regards

Ravi Jain

Super User
Posts: 2,684
Registered: ‎09-27-2017

Re: Median Based 3 columns values how it will work in Power BI

@jainravi

 

You can use this calculated column

 

Calculated Column =
MEDIANX (
    { [Corrected Aging Date], [End Hours], [Start Hours] },
    VALUE ( [Value] )
)
Super User
Posts: 2,684
Registered: ‎09-27-2017

Re: Median Based 3 columns values how it will work in Power BI

Member
Posts: 46
Registered: ‎04-06-2017

Re: Median Based 3 columns values how it will work in Power BI

Hi @Zubair_Muhammad,

 

Thanks for quick reply but my first column [Corrected Aging Date] is date type, how it will work when we have one column as Date type and other 2 are whole number type please guide me.

 

 

 

Member
Posts: 46
Registered: ‎04-06-2017

Re: Median Based 3 columns values how it will work in Power BI

Hi @Zubair_Muhammad

 

Sorry, I have missed the few things while post this, your example is right for when we are having a same data type columns.but in my Case  columns I am using are below type, 

 

[Corrected Aging Date]: Date time - Datatype  

[End hours ]: Time: Datatype

[Start hours]: Time: Datatype. 

 

You have provided an example above how it will work with MedianX with this datatype? Guide me. 

 

Community Support Team
Posts: 5,419
Registered: ‎09-21-2016

Re: Median Based 3 columns values how it will work in Power BI

Hi @jainravi,

 

If the three columns are date/time or time, then what the expect result should be returned by Median function? Could you show some examples in Excel?

 

Regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Super User
Posts: 2,684
Registered: ‎09-27-2017

Re: Median Based 3 columns values how it will work in Power BI

@jainravi

 

If you change the DataType of Calculated Column to DateTime.....I think you will get the desired result

But as Yuliana @v-yulgu-msft pointed out....please share few rows of data with expected results

Highlighted
Member
Posts: 46
Registered: ‎04-06-2017

Re: Median Based 3 columns values how it will work in Power BI

Hi @Zubair_Muhammad and @v-yulgu-msft 

 

I have tried with changing data type , but still it will not coming for me. Now no issues i have changed the way of calcuation for formula we needed as expected out come. As there is not any point to calculated median with 2 different datatypes. Thanks both fo you.