Dynamic Average Calculation from Percentage Range of Subject Value

[CoonPageDub]

Hi,

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, and also within the same group.

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%. 

Thank you!

[v-xicai]

Hi @CoonPageDub ,

>>Finally, you average the 5 buildings between those two 1900 Main to 123 Sesame and get 17. 

I am not sure what desired result would you want, could you please clarify more about how to get the result 17% and other desired output for further analysis? You may use formulas to display the calculation process to get 17%. You can also upload sample pbix to OneDrive and post the link here. Do mask sensitive data before uploading.

Please read this post to get your answer quickly: How to Get Your Question Answered Quickly.

Best Regards,

Amy 

Community Support Team _ Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[CoonPageDub]

Hi @v-xicai ,

My file is too big to upload, so I'll explain in individual steps. Please note that I added a couple holumns to help explain.

1.) The value/measure to be returned for 4401 Roger Street would be 15.4%.

2.) 4401 Roger Street's Building_Score = 945. You need to caclulate the percentile rank for Roger Street's Building Score, within that Group only. In the attached excel file, I have it sorted DESC so you get the idea. 

3.) 4401 Roger Street has a 37.5% percentile rank within the group subset of 8 buildings. 

4.) To help the description, I changed the range from 4% to 35%. So 135% of the 37.5% rank Roger Street has, is 50.6% - this is the "top value." 65% of 4401 Roger Street (1-35%), is 13.1%.

5.) The buildings that have a percentile rank between 50.6% - 13.1% is:

- 1800 Peachtree

- 4401 Roger Street

- 6 Center Street

6.) Now we need to calculate the weighted average of these buildings by adding the total vacant SF to the total building SF, which comes to 15.4%.

Does that help?

Thanks in advance! I've attached a link to the Excel calculations for ease right here: https://pagewood-my.sharepoint.com/:x:/p/paul_coonrod/EaWhP96Ikt1HmTAQKGQkPKkBEfWlOO-78AUdWVaQ83SA1Q...

Paul

[Greg_Deckler]

@CoonPageDub - Seems like a fairly straight-forward AVERAGEX with a FILTER of being in the same group and + or - 4%.

Measure = 

  VAR __Percent = <subject's percent however you get this slicer, etc.>

  VAR __Group = MAX('Table'[Group]) // or however you end up getting this

RETURN

  AVERAGEX(FILTER(ALL('Table'),[Group] = __Group && [Vacancy %] >= __Percent - .04 && [Vacancy %] <= __Percent + .04),[Vacancy %])

[CoonPageDub]

@Greg_Deckler Thanks for the help and taking the time, but unfortunately, I'm not following..

Can you be more explicit because I don't understand "subject's percent however you get this slicer" because I"m not using a slicer. Same thing with VAR _Group = I don't know what you mean by "however you end up getting this. 

Also, I'm using parameters for the "0.04." I don't understand how you're looking up a value based upon the building's score, but returning the historic vacancy %.

[CoonPageDub]

Any other help out there would be greatly appreciated! 

#LookingForAnswers_2020