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 UserPlease provide sanitized sample data that fully covers your issue. I cannot help you without usable sample data.
Please paste the data into a table in your post or use one of the file services like OneDrive or Google Drive. I cannot use screenshots of your source data.
Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523
Hello Ibendlin,
I manage to create a sample pbix file so you can check, my goal is create another measure to get the median of the mv per site per municipality (if not available per province). me initially thinking using mediax but that will entail me coming up two mediax for each site (one per municipality and one per province). another thing, there are unmatched site ids between td list and sid, my work around was to create a calculated column in the td list. Can you suggest another way to only count those matched site ids?
https://drive.google.com/drive/folders/1f0ew6RAl4s26fKzJFnNvy9CHaweTRpQR?usp=sharing
- lbendlin
I don't see what's wrong with your median calculation. Seems to work as designed?
Hello, it is getting the median of the tds and not the sum of tds per site. I suppose medianx(sid, calculate(sum(tdlist[Mv]) can address that however as you can see in the result of your powerbi file. it has no value for city of legaspi, oas and jovellar. the values for these municipality wll be using the median for the entire albay province instead. the thing is if we can optimize the measure i do not have to use the medianx twice? (i.e. per municipality and per province) is there a way to create a variable table showing the values per site then use median instead of medianx?
- lbendlin
Looks 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?