Calculation based off Live Data and a Static Table

Hi  EZimmet ,

Here are the steps you can follow：

1. Create a table with Enter data.

2. Create measure.

CAL =
var _count=COUNTX(FILTER(ALL(Tbl_Rack),'Tbl_Rack'[Site Name]="CAL"),[Site Name])
var _sum=SUMX(FILTER(ALL('Tbl_Max_Rack'),'Tbl_Max_Rack'[Site Name]="CAL"),[Max_Rack])
return
SWITCH(
    TRUE(),
    MAX('Table'[Expected])="Current",_count,
    MAX('Table'[Expected])="Max Allowed",_sum,
    MAX('Table'[Expected])="Available to install",_sum - _count
)

FLA =
var _count=COUNTX(FILTER(ALL(Tbl_Rack),'Tbl_Rack'[Site Name]="FLA"),[Site Name])
var _sum=SUMX(FILTER(ALL('Tbl_Max_Rack'),'Tbl_Max_Rack'[Site Name]="FLA"),[Max_Rack])
return
SWITCH(
    TRUE(),
    MAX('Table'[Expected])="Current",_count,
    MAX('Table'[Expected])="Max Allowed",_sum,
    MAX('Table'[Expected])="Available to install",_sum - _count
)

NYC =
var _count=COUNTX(FILTER(ALL(Tbl_Rack),'Tbl_Rack'[Site Name]="NYC"),[Site Name])
var _sum=SUMX(FILTER(ALL('Tbl_Max_Rack'),'Tbl_Max_Rack'[Site Name]="NYC"),[Max_Rack])
return
SWITCH(
    TRUE(),
    MAX('Table'[Expected])="Current",_count,
    MAX('Table'[Expected])="Max Allowed",_sum,
    MAX('Table'[Expected])="Available to install",_sum - _count
)

PHX =
var _count=COUNTX(FILTER(ALL(Tbl_Rack),'Tbl_Rack'[Site Name]="PHX"),[Site Name])
var _sum=SUMX(FILTER(ALL('Tbl_Max_Rack'),'Tbl_Max_Rack'[Site Name]="PHX"),[Max_Rack])
return
SWITCH(
    TRUE(),
    MAX('Table'[Expected])="Current",_count,
    MAX('Table'[Expected])="Max Allowed",_sum,
    MAX('Table'[Expected])="Available to install",_sum - _count
)

3. Result:

 

Best Regards,

Liu Yang

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