Adding sums from two measures

[sambgv]

Classification	Team	Role	Date	Value	ID_
X	A	Mechanic	07-Feb	1	A_Mechanic_Feb
X	B	Fireman	07-Feb	1	B_Fireman_Feb
X	C	Doctor	07-Feb	1	C_Doctor_Feb
X	Pool	Baker	07-Feb	2	Pool_Baker_Feb
X	Pool	Fireman	07-Feb	3	Pool_Fireman_Feb

The table above is the Supply Table. It shows what roles are assigned to project teams (Letter Teams). The Pool team includes roles that are not assigned to project teams, and are just in the resource pool

Team	Role	Date	Value	ID_team_role_mth
A	Mechanic	07-Feb	1	A_Mechanic_Feb
B	Fireman	07-Feb	1	B_Fireman_Feb
C	Doctor	07-Feb	1	C_Doctor_Feb

The Table Above is the Demand Table. It shows what roles are needed in project Teams (Letter Teams). It does not contain the Pool team. 

The table below is the BridgeTable. I created one to many relationship to both Supply and Demand table by the column Team_role_mth. 

What I need is 1). in a matrix show the difference in values between Supply and Demand (excluding the pool team) by the date columns.

2). in a matrix display the values of the pool team

3). in a bar chart the total of the 1 and 2 by month. For eg. (January Pool Value: 20) + (January Supply-Demand Value:5) = January Bar Chart 25.  And legend should include the classification, which is only present in the supply table. Filtering in the report should be available by Date, Role and Classification. The tables with uniques values for Role, Classification and Dates are presenet in the model. I would appreciate any suggestions. Thanks 

Team	Role	Date	Team_mth	Team_role_mth
A	Mechanic	07-Feb	A_Feb	A_Mechanic_Feb
B	Fireman	07-Feb	B_Feb	B_Fireman_Feb
C	Doctor	07-Feb	C_Feb	C_Doctor_Feb
Pool	Baker	07-Feb	Pool_Feb	Pool_Baker_Feb

[v-yangliu-msft · Accepted solution]

Hi  @sambgv ,

Here are the steps you can follow：

1. Create measure.

Question1 =
var _SupplyTable=
SUMX(FILTER(ALL('Supply Table'),YEAR('Supply Table'[Date])=YEAR(MAX('Supply Table'[Date]))&&MONTH('Supply Table'[Date])=MONTH(MAX('Supply Table'[Date]))&&'Supply Table'[Team]<>"Pool"),[Value])
var _DemandTable=
SUMX(FILTER(ALL('Demand Table'),YEAR('Demand Table'[Date])=YEAR(MAX('Supply Table'[Date]))&&MONTH('Demand Table'[Date])=MONTH(MAX('Supply Table'[Date]))),[Value])
return
_SupplyTable - _DemandTable

Question2 =
SUMX(FILTER(ALL('Supply Table'),YEAR('Supply Table'[Date])=YEAR(MAX('Supply Table'[Date]))&&MONTH('Supply Table'[Date])=MONTH(MAX('Supply Table'[Date]))&&'Supply Table'[Team]="Pool"),[Value])

Question3 =
var _Pool=
SUMX(
    FILTER('Supply Table',[Team]="Pool"),[Value])
var _Supply=
SUMX(
    'Supply Table',[Value])
var _Demand=
SUMX(  
    FILTER('Demand Table',
    YEAR('Demand Table'[Date])=YEAR(MAX('Supply Table'[Date]))&&MONTH('Demand Table'[Date])=MONTH(MAX('Supply Table'[Date]))),[Value])
return
_Pool + (_Supply - _Demand)

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

[sambgv]

the first requirnment is solved ( i think) by the following dax formula :

SUM(  'Supply' [Value] ) - SUM ('Supply' [Value])

2). I just :
CALCULATE (
Supply[Value],
TeamName = Pool
)

3). i tried adding these measure. and it works, but i can't add classification in the legend of the bar chart, since Classification is only in the supply table. i tried to add a column classification in the demand table, but it created dups in the bridge table, which cannot be since the bridge table is on the one side of the relationship