Calculation based off Live Data and a Static Table

[EZimmet]

Good Day,

I need to create a calculation off 2 tables #1 Live Data, #2 Static Data

Tbl #1 Need to sum Rack Counts for each Site
Tbl #2 Static number showing Max Racks that can be added to a Site
Output =  Max_Rack - Current Count = ______

Tbl #1
Live Data from the Site Rack Inventory
Tbl_Rack
Site Name	Status	AssetID
NYC	Active	Asset#001
NYC	Active	Asset#002
NYC	Active	Asset#003
NYC	Active	Asset#004
NYC	Active	Asset#005
FLA	Active	Asset#006
FLA	Active	Asset#007
FLA	Active	Asset#008
FLA	Active	Asset#011
CAL	Active	Asset#012
CAL	Active	Asset#013
CAL	Active	Asset#014
CAL	No Power	Asset#015
CAL	Active	Asset#016
CAL	Active	Asset#017
CAL	Active	Asset#018
CAL	Active	Asset#019
PHX	Active	Asset#020
PHX	Active	Asset#021
PHX	Active	Asset#022

Tbl #2
Static Tbl with Max Racks
Tbl_Max_Rack
Site Name	Max_Rack
NYC	10
FLA	10
CAL	15
PHX	15

Expected output
	CAL	FLA	NYC	PHX
Current	8	4	5	3
Max Allowed	15	10	10	15
Available to install	7	6	5	12

[Anonymous · Accepted solution]

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

[EZimmet]

Thank you - For your fast reply I was looking at doing this as one large SQL - but pulling it apart is a lot cleaner.