Bring Values from related table into Power Pivot

[Forrestgump1980]

Hi all, 

I have 2 tables:-

1. 'Requisition': Many rows joined to the 'ExternalFillRatio' table via field 'Division'
2. 'ExternalFillRatio': Joined to the 'Requisition' table via 'Division' 

I have set up a Power pivot which starts from my 'Requisition' table. I then want to bring in the field 'Ratio' from the 'ExternalFillRatio' table using a measure. Is this possible?

I was currenlt trying to achieve this using RELATED or RELATEDTABLE but im not sure this is the correct approach.

Any help greatly appreciated!

[bhanu_gautam · Accepted solution]

@Forrestgump1980 ,If you have a one-to-many relationship from 'ExternalFillRatio' to 'Requisition' (i.e., each division in 'ExternalFillRatio' can have multiple corresponding rows in 'Requisition'), you can use the RELATED function to bring in the 'Ratio' field.

RatioMeasure = RELATED(ExternalFillRatio[Ratio])

[Forrestgump1980 · Accepted solution]

Hi there, 

Thanks for your reply. So i have managed to add this to my 'Requisition' table as a column using:-

• As suggested RELATED(ExternalFillRatio[Ratio])
• Also managed to do this as a column using LOOKUPVALUE(ExternalFillRatio[Ratio],ExternalFillRatio[Division],Requisition[Division])

I assume your suggestion is better as there is a relationship, however with both solutions it sums the returns (which I dont want). I want it just to return the vaules. In Power Pivot excel I can't see an option to not summerize. Can I achieve this?

[bhanu_gautam · Accepted solution]

You can try it using pivot table

Create a PivotTable:

Insert a PivotTable in Excel using the data model.
Drag fields from the 'Requisition' table to the PivotTable.
Change Summarization to "Do Not Summarize":

When you drag the 'Ratio' field to the Values area of the PivotTable, it will default to summing the values.
To change this, click on the drop-down arrow next to the field in the Values area.
Select "Value Field Settings".
In the "Summarize Values By" tab, select "Do Not Summarize" (or "Show Values As" if available, and choose "No Calculation").

[bhanu_gautam · Accepted solution]

@Forrestgump1980 , Yes there are some limited option available in Power Pivot

[Forrestgump1980]

Unfortunately in power pivot excel in the 'Summarize Values by' drop down there is no option for 'Do Not Summerize' or  'No Calculation'. The only way I can see round it is to select Average....which seems to do the trick. Is that the best solution do you know in Power Pivot excel?

[bhanu_gautam · Accepted solution]

@Forrestgump1980 , Yes there are some limited option available in Power Pivot

[Forrestgump1980 · Accepted solution]

OK thanks for your help........I will just use average for now 

[bhanu_gautam]

@Forrestgump1980 , Can you please accept my reply as solution it will help others 

[bhanu_gautam · Accepted solution]

@Forrestgump1980 ,If you have a one-to-many relationship from 'ExternalFillRatio' to 'Requisition' (i.e., each division in 'ExternalFillRatio' can have multiple corresponding rows in 'Requisition'), you can use the RELATED function to bring in the 'Ratio' field.

RatioMeasure = RELATED(ExternalFillRatio[Ratio])

[Forrestgump1980 · Accepted solution]

Hi there, 

Thanks for your reply. So i have managed to add this to my 'Requisition' table as a column using:-

• As suggested RELATED(ExternalFillRatio[Ratio])
• Also managed to do this as a column using LOOKUPVALUE(ExternalFillRatio[Ratio],ExternalFillRatio[Division],Requisition[Division])

I assume your suggestion is better as there is a relationship, however with both solutions it sums the returns (which I dont want). I want it just to return the vaules. In Power Pivot excel I can't see an option to not summerize. Can I achieve this?

[bhanu_gautam · Accepted solution]

You can try it using pivot table

Create a PivotTable:

Insert a PivotTable in Excel using the data model.
Drag fields from the 'Requisition' table to the PivotTable.
Change Summarization to "Do Not Summarize":

When you drag the 'Ratio' field to the Values area of the PivotTable, it will default to summing the values.
To change this, click on the drop-down arrow next to the field in the Values area.
Select "Value Field Settings".
In the "Summarize Values By" tab, select "Do Not Summarize" (or "Show Values As" if available, and choose "No Calculation").