We help IT Professionals succeed at work.

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

190 Views
Last Modified: 2015-01-21
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
Comment
Watch Question

Director, Practice Manager and Computing Consultant
Awarded 2014
Top Expert 2014
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

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
Phillip BurtonDirector, Practice Manager and Computing Consultant
Awarded 2014
Top Expert 2014

Commented:
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().
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.