calculating median value of a column

[Maharshi Chatte]

Hi,

In the highlighted line i have calculated the avg of the request days calculated(highlighted in yellow). However I have to calculate the median value for the requested days. How can I achieve this?

var task = new GlideRecord('x_snc_aob_test1_request_baseline_input');
task.query();
while(task.next()){
var task1 = new GlideAggregate('x_snc_aob_test1_overall_request_status_1');
task1.addQuery('account',task.account_name);
task1.addQuery('overall_task_status','Completed');
task1.addAggregate('AVG','request_days');
task1.addQuery('onsite_offshore',task.onsite_offshore);
task1.groupBy('request_completed_month');
task1.query();

var req_days = '';
while(task1.next()){
req_days = task1.request_days + req_days;

var avg = task1.getAggregate('AVG','request_days');

var gr = new GlideRecord('x_snc_aob_test1_dashboard_baseline_input');
gr.addQuery('account_name',task.account_name);
gr.addQuery('onsite_offshore',task.onsite_offshore);
gr.addQuery('month',task1.request_completed_month);
gr.query();
while(gr.next()){
var arrayMonth = task1.request_completed_month.split(' ');
gr.month1 = arrayMonth[0];
//gs.info(task1.request_completed_year);
gr.year = arrayMonth[1];
gr.median_rate = avg;
gr.update();

}
}
}

Regards,

Maharshi Chatterjee

[Community Alums]

Hi @Maharshi Chatterjee ,

The median() function is a special application of the quantile() function that returns the median _value of an input table or all non-null records in the input table with values that fall within the 0.5 quantile (50th percentile) depending on the method used.

median() behaves like an aggregate function or a selector function depending on the method used.

median(
  column: "_value",
  method: "estimate_tdigest",
  compression: 0.0
)

When using the estimate_tdigest or exact_mean methods, it outputs non-null records with values that fall within the 0.5 quantile.

Also, refer to this link to learn more on this :

https://community.powerbi.com/t5/Power-Query/How-does-one-create-a-calulated-column-for-the-median-value-of-a/td-p/917084

Mark my answer correct & Helpful, if Applicable.

Thanks,

Sandeep

[Maharshi Chatte]

Hi @Sandeep Dutta ,

Thanks for your reply. Can you help me how can I add it in the script. We dont need the avg value now so we can replace it with median. Also in this script for each month there is a certain number of request days so we have to find the median of the request days for each month.

[Anil Lande]

Hi,

In simple term Median is the middle point of sorted array.

e.g if you have an array [1,4,5,6,11,45,56] then median would be 6.

In your case if you want median of request_days then get record in ascending order (use orderBy) and get row count. Row count will help you to get middle record request_days.

Thanks,

Anil Lande

[Maharshi Chatte]

Hi @Anil Lande  ,

Thanks for your reply. Can you help me how can I add it in the script. We dont need the avg value now so we can replace it with median. Also in this script for each month there is a certain number of request days so we have to find the median of the request days for each month.