summing with multiple filers and a related table

[dw700d]

I am trying to create a measure that calculates the Sum of all spend for “Type” (A) where “Transpo” equals(car, boat, truck , bike)  while excluding Accounts 9,10,11. Can anyone help me with this? * The Transpo Column is in a separate table*

Type	Spend	Account	Transpo Id
A	20,000,000	1	100
A	20,000,000	2	101
A	20,000,000	3	102
A	20,000,000	4	103
A	20,000,000	5	104
A	50,000,000	6	105
A	10,000,000	7	102
A	10,000,000	8	103
A	50,000,000	9	104
A	50,000,000	10	105
A	50,000,000	11	106
B	10,000,000	3	107
B	10,000,000	3	108

Transpo id	Transpo
100	car
101	Boat
102	Truck
103	Bike
104	Plane
105	Helicopter
106	Scooter
107	board
108	skate

[amitchandak · Accepted solution]

@dw700d , assuming table 1 and table 2 are connected

calculate(sum(Table[Spend]), filter(Table, Table[Type] ="A" && not(Table[Account] in {9,10,11})), filter(Table2, table2[Transpo] in{"car", "boat", "truck" , "bike"}))

[dw700d]

@amitchandakthank you, see the dax below

Total Non QOZ PO = CALCULATE( SUM('GSCS PO Commitment'[Total PO Value]),

FILTER('GSCS PO Commitment',[QOZ Status Type] ="Non-QOZ Location" &&

NOT 'GSCS PO Commitment'[Account Code]

IN{"2003.92C","2003.510C","2003.399C"}),FILTER('All Locations',{"CELL","CELL DAS","CELL CO","CELL NODE", "SMART CELL", "MICROWAVE"}))

[Anonymous · Accepted solution]

Hello @dw700d 
It seems like you missed the column name in the filter
FILTER('All Locations','All Locations'[Column]{"CELL","CELL DAS","CELL CO","CELL NODE", "SMART CELL", "MICROWAVE"}))

[amitchandak · Accepted solution]

@dw700d , assuming table 1 and table 2 are connected

calculate(sum(Table[Spend]), filter(Table, Table[Type] ="A" && not(Table[Account] in {9,10,11})), filter(Table2, table2[Transpo] in{"car", "boat", "truck" , "bike"}))

[dw700d]

Thank you @amitchandak 

My Tables are connected but I get the following message below

Any suggestions?

[amitchandak]

@dw700d , can share the formula, you used?