cancel
Showing results for
Did you mean:
Helper IV

## summing with multiple filers and a related table

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
2 ACCEPTED SOLUTIONS
Super User IV

@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"}))

Proud to be a Super User!

Resolver III

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"}))

5 REPLIES 5
Helper IV
@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"}))
Resolver III

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"}))

Super User IV

@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"}))

Proud to be a Super User!

Helper IV

Thank you @amitchandak

My Tables are connected but I get the following message below

Any suggestions?

Super User IV

@dw700d , can share the formula, you used?

Proud to be a Super User!

Announcements