Forum Discussion

AFra's avatar
AFra
Icon for Helper III rankHelper III
5 years ago
Solved

Create new table that summarizes table A filtered by table B

Hi all, 

 

I have a table A : 

ProjectStatusKPI sales
Astarted12
Aended70
Bstarted26
Bended40
Cstarted23
Cended120

 

And a table B : 

ProjectStatus

A

started
Bended
Cended

 

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

ProjectKPI sales

A

12
B40
C120

 

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 

Need Help
  • amitchandak's avatar
    amitchandak
    5 years ago

    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]))

     

     

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    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]))

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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