locked
How to create IF statement using two tables for the Rows field RRS feed

  • Question

  • Good morning, and thanks in advance for helping a new Power Pivot user.

    I have a Power Pivot table with two tables.  One table has a Revised Location field but some data is a generic "FOB".   If the cell equals "FOB" I want to use data from table two.

    Generically I'm trying to accomplish: IF(Revised Location="FOB", Inv FOB, Revised Location)

    I want the new location data to be in the Rows field. (e.g. Texas with all Texas sales below it)

    Both data points are text fields.  I haven't quite grasped how to create a calculated field using two tables and uncertain if an IF statement can be created within a calculated field or whether calculated means only mathematical.

    Assistance is appreciated.

    Friday, December 14, 2018 4:17 PM

All replies

  • Excel 365 Pro Plus with PowerPivot and Power Query (aka Get & Transform)
    Calculated Column with Related()
    http://www.mediafire.com/file/18bifrkal0k2l59/12_17_18.xlsx/file
    http://www.mediafire.com/file/flnh1jo5i0ccgh8/12_17_18.pdf/file

    Monday, December 17, 2018 5:06 PM
  • Hello!

    If there is a common field in both tables, then you can link the two tables in the  PowerPivot data model.  Next, you can do a few different things to reach your result.  I am a basic-level hack so  I tend to do things the hard way. 

    In your first table, add a column  with a calculation that will deliver the value from the related table two; then name it "REV_LOC" ... use  "=RELATEDTABLE" function to do this.  When you type =RELATEDTABLE, you will get to choose the field showing the location you want to display if the criteria is met of the next statement we are going to build.  The next column you add is your IF statement:  IF [Revised Location]="FOB", THEN [REV_LOC].  The logic will deliver the result from the column, which is drawing the data from the related table.

    Follow me?

    John

    Tuesday, December 18, 2018 3:08 PM