cancel
Showing results for
Did you mean:
Helper V

## 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

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

Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!
Skilled Sharer

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

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

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

Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!
Helper V

Thank you @amitchandak

My Tables are connected but I get the following message below

Any suggestions?

Super User

@dw700d , can share the formula, you used?

Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

Announcements

#### 2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.