Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 181
  • Last Modified:

How do you return multiple results to one cell using powerpivot for excel?

Hi Experts,

I have two tables in Powerpivot for Excel 2013 that I would like to combine into one report.

The example in the attached file may explain what I require, however this is only sample data due to the original data being commercially sensitive.

My question refers to the calculation syntax that I need to achieve "Data to Encapsulate" within the "desired result" section. As there are many results (some duplicated) I cannot simply use =RELATED(Data to Encapsulate) as this throws an error.

This link Link does seem to be similar to what I require, however it seems awfully complicated and, as I am looking up in another table, difficult to alter.

Thanks in advance.
EE-Question.docx
0
martinibbo
Asked:
martinibbo
  • 2
1 Solution
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
It may seem complicated, but it's probably the only way to go. I don't know if it will eliminate duplicates though...

I can answer one bit of your question directly, though:

>>  As there are many results (some duplicated) I cannot simply use =RELATED(Data to Encapsulate) as this throws an error

This is because you are going from one-to-many, instead of many-to-one. In this case, use RELATEDTABLE() instead of RELATED().
0
 
martinibboAuthor Commented:
OK thanks, I will try to amend to suit my needs then.

researching into RELATEDTABLE() it appears that I need another formula to prefix it such as =SUMX(RELATEDTABLE(),).

This will only work with values, if the data I want is text (Such as in my example), is there a way to return this, even if its on different rows? (i.e. one row per result and not concatenated as in my original question)

Sorry, I am very new to powerpivot!

Thanks
0
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
The PATH() function in the link you provided should work with RELATEDTABLE, as it works with columns. See https://msdn.microsoft.com/en-us/library/gg492167.aspx for information on PATH().
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now