Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I have a table that has a list of points (latitude and longitude) and for each point I have the price of a house, the code of the state and the code of the county.
Then I've defined a calculated column ("Price factor") that returns, for each point, a value between 0 and 1 based on the house price of that point and the maximum and minimum observed in the whole column of the prices.
What I want to do is, when applying filters, the scale of the filtered table be 0 and 1.
For example, I have the following table:
State | County | Point code | Lat | Long | Price | Price factor |
CA | San Bernardino | 1 | ||||
CA | San Bernardino | 2 | ||||
CA | San Bernardino | 3 | ||||
CA | Riverside | 4 | ||||
CA | Riverside | 5 | ||||
CA | Riverside | 6 | ||||
CA | Imperial | 7 | ||||
CA | Imperial | 8 | ||||
CA | Imperial | 9 | ||||
NV | Clark | 10 | ||||
NV | Clark | 11 | ||||
NV | Clark | 12 | ||||
NV | Nye | 13 | ||||
NV | Nye | 14 | ||||
NV | Nye | 15 | ||||
NV | Lincoln | 16 | ||||
NV | Lincoln | 17 | ||||
NV | Lincoln | 18 | ||||
AZ | Mohave | 19 | ||||
AZ | Mohave | 20 | ||||
AZ | Mohave | 21 | ||||
AZ | La Paz | 22 | ||||
AZ | La Paz | 23 | ||||
AZ | La Paz | 24 | ||||
AZ | Yuma | 25 | ||||
AZ | Yuma | 26 | ||||
AZ | Yuma | 27 |
What I want is to, when filtered for the state of Arizona (AZ), the maximum value recorded for this state as a "Price factor" of 1 and the minimum value for that state as a "Price factor" of 0.
The same when I filter for province.
I'd really appreciate the help.
Ze Mario
Solved! Go to Solution.
please see the file here: https://1drv.ms/u/s!AiiWkkwHZChHj2CXi2GAio_o149h
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
I think I have understood your requirement more or less. Can you post a data sample containing data also for the 4 columns you've left blank? maybe also a table with expected results?
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
Hi @LivioLanzo, thank you for the prompt response.
The table with a sample of data is:
State | County | Point code | Lat | Long | Price | Price factor |
CA | San Bernardino | 1 | 1050 | 0,448 | ||
CA | San Bernardino | 2 | 1020 | 0,403 | ||
CA | San Bernardino | 3 | 1200 | 0,672 | ||
CA | Riverside | 4 | 1300 | 0,821 | ||
CA | Riverside | 5 | 1030 | 0,418 | ||
CA | Riverside | 6 | 900 | 0,224 | ||
CA | Imperial | 7 | 750 | 0,000 | ||
CA | Imperial | 8 | 1090 | 0,507 | ||
CA | Imperial | 9 | 1350 | 0,896 | ||
NV | Clark | 10 | 1150 | 0,597 | ||
NV | Clark | 11 | 1420 | 1,000 | ||
NV | Clark | 12 | 850 | 0,149 | ||
NV | Nye | 13 | 750 | 0,000 | ||
NV | Nye | 14 | 1020 | 0,403 | ||
NV | Nye | 15 | 1060 | 0,463 | ||
NV | Lincoln | 16 | 950 | 0,299 | ||
NV | Lincoln | 17 | 1050 | 0,448 | ||
NV | Lincoln | 18 | 860 | 0,164 | ||
AZ | Mohave | 19 | 1010 | 0,388 | ||
AZ | Mohave | 20 | 1000 | 0,373 | ||
AZ | Mohave | 21 | 900 | 0,224 | ||
AZ | La Paz | 22 | 1080 | 0,493 | ||
AZ | La Paz | 23 | 900 | 0,224 | ||
AZ | La Paz | 24 | 1050 | 0,448 | ||
AZ | Yuma | 25 | 1000 | 0,373 | ||
AZ | Yuma | 26 | 950 | 0,299 | ||
AZ | Yuma | 27 | 790 | 0,060 |
Lat and Long columns are just the coordinates of the points. The price factor column is equal to (Price - Min(Price))/(Max(Price)-Min(Price)).
What I want is for the price factor column values to range from 0 to 1 by aplying filters (for both state and county in this case). For exemple, if apply the filter to AZ, the results should be as follows:
State | County | Point code | Lat | Long | Price | Price factor |
AZ | Mohave | 19 | 1010 | 0,759 | ||
AZ | Mohave | 20 | 1000 | 0,724 | ||
AZ | Mohave | 21 | 900 | 0,379 | ||
AZ | La Paz | 22 | 1080 | 1,000 | ||
AZ | La Paz | 23 | 900 | 0,379 | ||
AZ | La Paz | 24 | 1050 | 0,897 | ||
AZ | Yuma | 25 | 1000 | 0,724 | ||
AZ | Yuma | 26 | 950 | 0,552 | ||
AZ | Yuma | 27 | 790 | 0,000 |
and if I apply the filter to Yuma, the results should be:
State | County | Point code | Lat | Long | Price | Price factor |
AZ | Yuma | 25 | 1000 | 1,000 | ||
AZ | Yuma | 26 | 950 | 0,762 | ||
AZ | Yuma | 27 | 790 | 0,000 |
My goal is to make a heatmap that is scaled for the geographic area I'm filtering to.
Thank you again for the response,
Ze Mario
please see the file here: https://1drv.ms/u/s!AiiWkkwHZChHj2CXi2GAio_o149h
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
81 | |
70 | |
62 |
User | Count |
---|---|
148 | |
116 | |
104 | |
90 | |
65 |