Look up data from PowerPivot table outside of pivot table

[domtrump]

So here is what I need to do: I have a pivot table which I created from a powerpivot table (Excel 2010). It has the customer's account number in the first column. In the last column of the report, after all of the measures, I need to list the customer's sales rep. Obviously, a pivot table will not allow me to add dimension column at the END of the report, so what I do with a normal pivot table is put a column to the right, outside of the pivot table, and just do a vlookup to get the sales rep.

However, the table that powerpivot creates does not appear to be a named range in the excel workbook itself so I cannot reference it in the vlookup. Is there some kind of DAX magic or GETDATA that will allow me to reference the powerpivot table and lookup the sales rep from the customer number? Any other suggestions on how to accomplish this? Thanks.

Sample:

Excel Workbook
	S	T	U	V	W	X	Y	Z	AA	AB	AC
6	Pivot Table	*	*	*	*	*	*	*	*	*	Outside Pivot Table
7	ACCT #	2015	2014	Var	2015	2014	Var	2015	2014	Var	RepLName
8	3866937	*234,765	* *54,009	*180,756	* * 4,028	* * 1,159	* * 2,869	* *21,430	* *(4,271)	* *25,701	JOE SMITH
9	5042761	*	*148,950	(148,950)	*	* * 2,234	* *(2,234)	*	* (22,375)	* *22,375	JOHN SMITH
10	1257374	*	*180,820	(180,820)	*	* * 2,712	* *(2,712)	*	* (20,420)	* *20,420	BOB SMITH
1

 

[PentaGalCXO]

Depending on the complexity of your pivot table, perhaps you can build it with a DAX Table Query. From that approach you can add most any related column in any order. There are limitations such as no slicers, but I use this approach quite often. Speed is a bonus. Especially for large/wide tables a DAX Table Query will run laps around a (power) pivot table. For an example see TinyLizards blog: DAX Table Query Example | Power Pivot | Tiny Lizard

 

[domtrump]

Depending on the complexity of your pivot table, perhaps you can build it with a DAX Table Query. From that approach you can add most any related column in any order. There are limitations such as no slicers, but I use this approach quite often. Speed is a bonus. Especially for large/wide tables a DAX Table Query will run laps around a (power) pivot table. For an example see TinyLizards blog: DAX Table Query Example | Power Pivot | Tiny Lizard

Looks like it only works in 2013 so I'm probably going to have to find another approach to use with 2010. Thanks for the info - I'll check it out.

-Dom

 

[Ozeroth]

You could always create a measure that returns the Sales Rep as text.
A table relating sales reps to accounts would need to be added to the Data Model.

Then you could use something like

Sales Rep measure := IF( HASONEVALUE( RELATED(Lookuptable[Sales Rep]) ), RELATED(Lookuptable[Sales Rep]) )

 

[domtrump]

You could always create a measure that returns the Sales Rep as text.
A table relating sales reps to accounts would need to be added to the Data Model.

Then you could use something like

Sales Rep measure := IF( HASONEVALUE( RELATED(Lookuptable[Sales Rep]) ), RELATED(Lookuptable[Sales Rep]) )

but that wouldn't allow me to put the data to the right of the pivot table??

 

[Ozeroth]

As long as you define Sales Rep as a measure, it can be added as the rightmost value field in the pivot table after all your numeric measures.

Sorry wasn't thinking with my use of RELATED...the important part of the measure should be something like IF( HASONEVALUE([Sales rep]), VALUES([Sales rep]) )

 

[scottsen]

For extra clarity here... the learning is that measures can return text. Your other options is just convert the pivot table to cube formulas and break free of the constrains of pivot tables...

 

[domtrump]

So (I'm very new to DAX), what is the syntax of the measure? Where does the IF statement you suggested go?

 

[domtrump]

I entered the formula: Rep Code:=IF( HASONEVALUE([REP]), VALUES([REP]) ). REP is the name of the column that contains the sales rep. I get the following error:

The value for 'REP' cannot be determined. Either 'REP' doesn't exist, or there is no current row for a column named 'REP'

Where am I going wrong?

 

[Ozeroth]

Include the table name before rep and it should work. e.g. MyTable[REP]
Probably safest to include table name whenever referencing a column (sorry left this off my example before).