Create IF and SUMIFS between Two Tables

[Anonymous]

Hi Guys,

Please, i need some help!

I'm new on Power BI and my "Excel-Mind" stragule me.

Well,  i ahve two tables, with the informations below:

Table 1:

[IMG]http://i68.tinypic.com/20k7qiq.png[/IMG]

Table 2:

[IMG]http://i68.tinypic.com/15fhzlg.png[/IMG]

Basically i have to put a new column on the Table 2 with:

IF the Status is Accomplished so SUM the Amount Paid on the Table 1 relative to the Client, Month and Year;

Like this on Excel: 

=IF([Status]="Accomplished";SUMIFS(Table1[Amount Paid];Table1[Client Code];Table2[Client Code];Table1[Year];Table2[Year];Table1[Month];Table2[Month];Table1[Rule];"Negociated")

So, i try everithing i know (not so much, ><) and i can't connect the two tables with CALCULATE, FILTERS and etc.

Plz,

Can you help guys??

Tks,

[Ashish_Mathur]

Hi,

Try this calculated column formula in Table2

=IF([Status]="Accomplished",CALCULATE(SUM(Table1[Amount Paid]),FILTER(Table1,Table1[Client Code]=EARLIER(Table2[Client Code])&&Table1[Year]=EARLIER(Table2[Year])&&Table1[Month]=EARLIER(Table2[Month])&&Table1[Rule]="Negociated")),BLANK())

You may have to replace , with ;

Hope this helps.