Script

[RealEstate2018]

Hi Guys,

I was hoping someone can help me figure out how to modify the formula shown below. Currently, the formula shown      = num((sum([Total Number of Attorneys])+sum([Total Non-Attorneys])+sum([Sublet Lawyer Offices]))/sum([Maximum Attorney Capacity]),'###.%');  

This formula gives you a percentage of how well attorney capacity is utilized.  If someone can help me exclude Beijing and Hong Kong, that will be amazing!

I want to know if there is a way to exclude specific cities, meaning, give me total utilization percentage of all cities (excluding Beijing and Hong Kong as an example) 

The formula is input into a text object so I don't have any way to filter things so the formula needs to be set to exclude directly within the formula.  All locations are identified under a common indicator known as "City" (I've attached a copy from a Bar chart as an example)

[lockematthewp · Accepted solution]

You can accomplish this using set analysis. Your formula would be:

num((sum({<City-={'Beijing', 'Hong Kong'}>}[Total Number of Attorneys])+sum({<City-={'Beijing', 'Hong Kong'}>}[Total Non-Attorneys])+sum({<City-={'Beijing', 'Hong Kong'}>}[Sublet Lawyer Offices]))/sum({<City-={'Beijing', 'Hong Kong'}>}[Maximum Attorney Capacity]),'###.%')