Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2015-01-20
3
Medium Priority
?
178 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 1500 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

Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

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

My attempt to use PowerShell and other great resources found online to simplify the deployment of Office 365 ProPlus client components to any workstation that needs it, regardless of existing Office components that may be needing attention.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

715 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