Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

IF formula with multiple conditions

I have 2 tables and 1 country table which is used to create relationship between them (even other tables linked to country table).

Now i need to check if Amt is available in Table1 for the same country in Table2, if available then i need to take sum of Table2[Amt]/table1[Amt] else 0.

 

i have single direction relationship between Table2 & country and bothside direction relationship between Table1 & Country.

 

in excel i can use cell reference to do this (ex:=IF(F8=0, 0,AJ8/F8)) but am unble to do this in power bi. please assist

 

Table1:

RegionAmt
Greater China100
SEA-K140
India345
Japan235
South Pacific34

 

Table2:

RegionAmt
Greater China57
SEA-K34
India23
Japan57
South Pacific23
India34
Japan42

 

Country: table

Country
Region
Greater China
SEA-K
India
Japan
South Pacific

 

 

Output table expeted:

RegionAmtDivide Amt
Greater China570.57
SEA-K340.242857143
India570.165217391
Japan990.421276596
South Pacific230.676470588
Need Help
  • PattemManohar's avatar
    PattemManohar
    7 years ago

    Anonymous  Please try this as a New Table (below logic uses your table 1 as lookup and table 2 as base table)

     

    Test297Out = 
VAR _Table = SUMMARIZE(Test297Main,[Region],"Amt",SUM(Test297Main[Amt]))
RETURN ADDCOLUMNS(_Table,"DivideAmount",DIVIDE([Amt],LOOKUPVALUE(Test297Lkp[Amt],Test297Lkp[Region],Test297Main[Region])))

     

     

1 Reply

  • PattemManohar's avatar
    PattemManohar
    Community Champion
    7 years ago

    Anonymous  Please try this as a New Table (below logic uses your table 1 as lookup and table 2 as base table)

     

    Test297Out = 
VAR _Table = SUMMARIZE(Test297Main,[Region],"Amt",SUM(Test297Main[Amt]))
RETURN ADDCOLUMNS(_Table,"DivideAmount",DIVIDE([Amt],LOOKUPVALUE(Test297Lkp[Amt],Test297Lkp[Region],Test297Main[Region])))