Calculated Column

[CoonPageDub]

Howdy Folks!

I need help with a technique to change a varaiable on a calculated column. I have a table that provides demographic information (15 columns) associated with individual properties (30,000). 

Right now, here is my process for scoring:

1.) Rank each property from 1-30,000 using the RankX function

2.) Calculate the percentile for each building from 0.00-1.00.

3.) Multiply each of these percentiles by 100 to get a "score" for each category

4.) Add each of the 15 categories/columns together to get a "demographic score." 

5.) I'm adding the demographic score to other scores in my model associated with the buildings.

I need to add weights/parameters from 10%-100% to each of the 15 categories so I can test the outcomes accordingly. I initially did this all with measures, but whenever I added the demographic score to other scores, my computer would run out of RAM. I just upgraded to get 64 MB of RAM. 

So I think I need to use calculated columns, and then use SUMMARIZE to store demographic score with each iteration, rather than measures.

Therefore my question is: How can I add variables/parameters to each of my 15 calculated columns? Measures are not an option. Keep in mind that I do not need to do this dynamically; I can reload or update another query if need be.

Thank you!

[v-xuding-msft]

Hi @CoonPageDub ,

Can you please share some sample data and your expected output? I will understand clearly about your requirement. Thx.

[CoonPageDub]

Great! I need help writing the formula to calculate the average vacancy for all buildings within the subject building's Score that are 4% higher and 4% lower of the subject building.

Building = Specific Property

Group = Groupings of properties

Score = Score generated via other metrics and stored as a measure

Vacancy = generated from another table

Building	Group	Score	Vacancy %
50 Smith	B	1200	14%
3737 Speedster	B	1150	40%
1200 Broadway	B	1000	25%
1900 Main St	B	995	3%
1800 Peachtree	B	962	10%
4401 Roger St.	B	945	9%
6 Center St	B	944	40%
123 Sesame	B	940	25%
1 A St.	A	1255	11%
2 B St	A	1252	3%
3 C Lane	A	1209	8%
20 Kelly Circle	A	1175	17%
1000 Main St	A	1155	7%
2727 Allen	A	1153	1%
1276 Kermit	A	990	1%
1300 Congress	A	960	16%
1333 Westway	A	957	16%
84 Elmo	A	940	3%
11212 Honda	A	935	10%
711 Right	A	932	16%
888 Triple	A	932	16%
76666 Ralph	A	920	9%

For example the value to be returned for 4401 Roger St. would be 17%. Keep in mind this needs to be done within the Group, not for all buildings, just the buildings that are 4% higher and lower than the subject. 4401 Roger St.'s building score is 945, so 104% = 983. The upper limit building would be 1900 Main. That score is 995, so that differnce of 12 is smaller than 1800 Peachtree's score of 962 (difference of 21 from 983).  You then would do the same calculation for the lower limit. Finally, you average the 5 buildings between those two 1900 Main to 123 Sesame and get 17. 

Another example is 1300 Congresss and the value returned would be 11%. 

Does this help?