Create new table that summarizes table A filtered by table B

[AFra]

Hi all, 

I have a table A : 

Project	Status	KPI sales
A	started	12
A	ended	70
B	started	26
B	ended	40
C	started	23
C	ended	120

And a table B : 

Project	Status
A	started
B	ended
C	ended

I need to create a table C that summarizes A (Project and KPI sales) filtred by B (status), such as : 

Project	KPI sales
A	12
B	40
C	120

I guess it will be something like 

New table = summarize(filter... status from A = status from B)...  but I can't get it right. 

Thanks in advance for your help! 

Ana 

[v-jayw-msft]

Hi @AFra ,

Second formula should work, has the problem be solved?

If it works for you, please consider to mark it as solution.

Best Regards,

Jay

[amitchandak · Accepted solution]

@AFra , If they joined on project Id then try first one

summarize(filter('table A', 'table A'[Status] ='table B'[Status] ), 'table A'[Project] , "sales" ,Sum('table A'[sales]))

or

summarize(filter(crossjoin('table A',selectcolumns('tableb', "pj" ,[Project], "st",[Status] )), [Status] =[St] && [Project] =[pj] ), 'table A'[Project] , "sales" ,Sum('table A'[sales]))