Solved

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

Posted on 2015-01-20
3
173 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
0
Comment
Question by:martinibbo
  • 2
3 Comments
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 500 total points
ID: 40561471
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
 

Author Comment

by:martinibbo
ID: 40561497
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
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40561513
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

Featured Post

How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

713 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question