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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
josemariogomes
Frequent Visitor

Max and Min of a column based on filters

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:

 

StateCountyPoint codeLatLongPricePrice factor
CASan Bernardino1    
CASan Bernardino2    
CASan Bernardino3    
CARiverside4    
CARiverside5    
CARiverside6    
CAImperial7    
CAImperial8    
CAImperial9    
NVClark10    
NVClark11    
NVClark12    
NVNye13    
NVNye14    
NVNye15    
NVLincoln16    
NVLincoln17    
NVLincoln18    
AZMohave19    
AZMohave20    
AZMohave21    
AZLa Paz22    
AZLa Paz23    
AZLa Paz24    
AZYuma25    
AZYuma26    
AZYuma27    

 

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

1 ACCEPTED SOLUTION

Hi @josemariogomes 

 

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!  

View solution in original post

4 REPLIES 4
LivioLanzo
Solution Sage
Solution Sage

Hi @josemariogomes 

 

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,

 

 

StateCountyPoint codeLatLongPricePrice factor
CASan Bernardino1  10500,448
CASan Bernardino2  10200,403
CASan Bernardino3  12000,672
CARiverside4  13000,821
CARiverside5  10300,418
CARiverside6  9000,224
CAImperial7  7500,000
CAImperial8  10900,507
CAImperial9  13500,896
NVClark10  11500,597
NVClark11  14201,000
NVClark12  8500,149
NVNye13  7500,000
NVNye14  10200,403
NVNye15  10600,463
NVLincoln16  9500,299
NVLincoln17  10500,448
NVLincoln18  8600,164
AZMohave19  10100,388
AZMohave20  10000,373
AZMohave21  9000,224
AZLa Paz22  10800,493
AZLa Paz23  9000,224
AZLa Paz24  10500,448
AZYuma25  10000,373
AZYuma26  9500,299
AZYuma27  7900,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:

 

StateCountyPoint codeLatLongPricePrice factor
AZMohave19  10100,759
AZMohave20  10000,724
AZMohave21  9000,379
AZLa Paz22  10801,000
AZLa Paz23  9000,379
AZLa Paz24  10500,897
AZYuma25  10000,724
AZYuma26  9500,552
AZYuma27  7900,000

 

and if I apply the filter to Yuma, the results should be:

 

StateCountyPoint codeLatLongPricePrice factor
AZYuma25  10001,000
AZYuma26  9500,762
AZYuma27  7900,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

Hi @josemariogomes 

 

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!  

Hi @LivioLanzo ,

 

Thank you very much. That was exactly what I wanted to do. 

 

Kind regards,

 

Ze Mario

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.