Forum Discussion
Bring Values from related table into Power Pivot
Hi all,
I have 2 tables:-
- 'Requisition': Many rows joined to the 'ExternalFillRatio' table via field 'Division'
- '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!
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])
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?
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").Forrestgump1980 , Yes there are some limited option available in Power Pivot
OK thanks for your help........I will just use average for now
7 Replies
- bhanu_gautam
Super User
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])
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
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").
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
Forrestgump1980 , Yes there are some limited option available in Power Pivot
OK thanks for your help........I will just use average for now