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:
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 | |||||||||||||
