Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
Hi @CoonPageDub ,
Can you please share some sample data and your expected output? I will understand clearly about your requirement. Thx.
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?
User | Count |
---|---|
93 | |
84 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |