Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Create IF and SUMIFS between Two Tables

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,

Need Help
Show and Tell
Tips and Tricks
Tutorial Requests

1 Reply

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    7 years ago

    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.