Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
newbiepowerbi
Helper II
Helper II

Get the median based on certain column

Hello im trying to compute median of the market value (MV) for each of site based on municipality which the site is located, and if there is no record of MV for the particular municipality, get the median based on the province which the site is located. 

 

I have 3 tables:

 

LGU List
ProvinceMunicipalityLGU Code

 

SID
Site IDLGU Code

 

Consolidated TD List
Site IDTD NoProperty TypeMV

 

LGU list has one to many relationship with SID using LGU Code, wile SID has one to many relationship with Consolidated TD List using site ID.
I tried to come up with a measure like this:


 

Median Measure = 
VAR MedMun=
CALCULATE(
    MEDIAN('Consolidated TD List'[MV]),
    'Consolidated TD List'[TDNo] <> BLANK(),
    CONTAINSSTRING('Consolidated TD List'[Property Type], "Building"),
    REMOVEFILTERS('LGU List'[LGU Code]),
    VALUES('LGU List'[Municipality])
)
VAR MedProv =
CALCULATE(
    MEDIAN('Consolidated TD List'[MV]),
    'Consolidated TD List'[TDNo] <> BLANK(),
    CONTAINSSTRING('Consolidated TD List'[Property Type], "Building"),
    REMOVEFILTERS('LGU List'[LGU Code]),
    VALUES('LGU List'[Province])
)
RETURN
IF(
    MedMun>0,
    MedMun,
    MedProv
) 

 

Somehow it is not working. My visual is a simple one, list of site ids and the median, with grandtotal. Let me know what needs to change. Thanks

2 ACCEPTED SOLUTIONS

Median = COALESCE(medianx(TDList,TDList[MV]),CALCULATE(medianx(TDList,TDList[MV]),REMOVEFILTERS(LGUlist[Municipality])))

View solution in original post

ok thanks will check the measure will get back to you if i encounter any issues. anyway many thanks for your time

View solution in original post

13 REPLIES 13
lbendlin
Super User
Super User

Please 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 

I don't see what's wrong with your median calculation. Seems to work as designed?

 

lbendlin_0-1659566319514.png

 

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?

 

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?

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). 

 

newbiepowerbi_0-1659617593732.png

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.  

 

Ah so you want the MEDIANX() for the SUM per category, not for individual values?  

 

lbendlin_0-1659640839903.png

 

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

No, still not clear. Quite the opposite. Maybe you can walk me through a particular example.

Hello please screenshot below.

 

Screenshot (45).png

For Camalig, since it has 3 sites with MV, the median measure should get 2319620 (the median per site for that particular municipality). Meanwhile, for city of legaspi, since it does not have sites with MV, the measure should get all the individual MV for each site for the entire province of albay then get the median. 

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

made some minor modification but thanks for the help 

Median Value = 
VAR PerMuni = 
CALCULATE(
    MEDIANX(
        SID,
        [MV Value]
    ),
    REMOVEFILTERS(SID[SiteID])
)
VAR PerProv =
CALCULATE(
    MEDIANX(
        SID,
        [MV Value]
    ),
    REMOVEFILTERS(SID[SiteID]),
    REMOVEFILTERS(LGUlist[Municipality])
)
Return
COALESCE(PerMuni,PerProv)*[No of Sites]

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Users online (181)