Forum Discussion
Get the median based on certain column
Median = COALESCE(medianx(TDList,TDList[MV]),CALCULATE(medianx(TDList,TDList[MV]),REMOVEFILTERS(LGUlist[Municipality])))ok thanks will check the measure will get back to you if i encounter any issues. anyway many thanks for your time
Super UserLooks like I still don't understand what you are trying to achieve, and why. Are you maybe looking for the average instead of the median?
Hello,
As you can see in the pbix file i sent to you, each site contains 2 or more TDs with different market values. I need to get the sum of all these tds for each site to get the market value per site. then for all the sites located in the specific municipality, get the median (not the average since i want to remove all possible outlier values that will distort the average value).
Please see the above screenshot, value of median is different if it simply based on the tds rather than the sum of the tds per site. As mentioned again, i know i can do medianx to achieve the result, however there are certain municipalities which do not have tds hence i can't get the median for that particular municipality. As remedy, i will need to get the median for the entire province. if that is the case that will mean i will also need to do medianx on per province. so my goal is to have an optimal measure that will allow me to get the median per lgu, if available, or else get the median per province. I hope this clears up my query.
- lbendlin
Ah so you want the MEDIANX() for the SUM per category, not for individual values?
sorry for the late reply, i will need to get the median per site for the entire municipality except those without mv such as jovelar, oas, and city of legaspi in which i will need to get the median per site for the entire province. Hope this clears things up. thanks
- lbendlin
No, still not clear. Quite the opposite. Maybe you can walk me through a particular example.