Forum Discussion
DAX Median House Price by Postcode
Hi,
Long time lurker and first time poster. Firstly, want to extend my appreciation for the help this forum has provided me already on my PBI journey, from power query to DAX.
I am trying to formulate a median price by postcode in DAX. The data set that I have available lists the individual house sale by postcode. Within the visual page I can easily create a table with the number of sales by postcode and the median price from the postcode.
However I need to set up a column using DAX that does the same thing, and using an individual house sales by postcode to automatically find the the median price.
The reason I want to do this is because when I visualise the postcode in a map and categorise using switch(true by price. The map colours pick up the minimum house price sale. As an example the median price by postocde (30 individual sales) could be $500k = Red, but the smallest individual sale in that postcode is $250k = blue. Therefore the map legend returning a blue postcode.
How do I create a dynamic median price by postcode to return the correct colour in the map visual?
Any help or advice would be greatly appreciated.
4 Replies
- Greg_Deckler
Community Champion
dschin02 Well, columns are not dynamic but are calculated at time of data refresh. If that is OK then you can use a column. Measures are calculated on the fly. So, there are a lot of unknowns here. Does anything below the median get a blue color and anything at or above the median a red color? I don't get the rules for colorization. Also, sample data tends to REALLY help.
Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.Thanks Greg_Deckler ,
At this stage I am looking to just get a median house price by postcode, I would be happy to do this in a measure or a column. Is there an equivalant in DAX of Averageif(range,criteria,average range).- Greg_Deckler
dschin02 Use MEDIANX(FILTER(...), ...)
