Lookup in PowerPivot by rows and columns

[Kaasiic]

Hello!

I'll start by saying that I'm completely new with PowerPivot and therefor I'm curently trying to understand what I can and can't complete using this program.
I was wondering is it possible to achieve in PowerPivot tables the same thing I can achieve in Excel by using together Sumifs, Indirect and Match functions.
I have two tables: first contains sales data, second contains discount data.

Table 1
[TABLE="width: 500"]
<tbody>[TR]
[TD]Month[/TD]
[TD]Customer[/TD]
[TD]Product[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Customer 1[/TD]
[TD]Product A[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Customer 2[/TD]
[TD]Product A[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Customer 3[/TD]
[TD]Product B[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Customer 1[/TD]
[TD]Product C[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Customer 2[/TD]
[TD]Product A[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Customer 1[/TD]
[TD]Product B[/TD]
[/TR]
[TR]
[TD]etc.[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Table 2
[TABLE="width: 500"]
<tbody>[TR]
[TD]Customers[/TD]
[TD]Products[/TD]
[TD]Month 7[/TD]
[TD]Month 8[/TD]
[TD]Month 9[/TD]
[TD]etc.[/TD]
[/TR]
[TR]
[TD]Customer 1[/TD]
[TD]Product A[/TD]
[TD]5%[/TD]
[TD]5.5%[/TD]
[TD]4.8%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Customer 1[/TD]
[TD]Product B[/TD]
[TD]7%[/TD]
[TD]7%[/TD]
[TD]6.3%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Customer 1[/TD]
[TD]Product C[/TD]
[TD]3%[/TD]
[TD]3.8%[/TD]
[TD]4.2%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Customer 2[/TD]
[TD]Product A[/TD]
[TD]9%[/TD]
[TD]8.5%[/TD]
[TD]9.8%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Customer 3[/TD]
[TD]Product A[/TD]
[TD]2%[/TD]
[TD]3%[/TD]
[TD]2%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Customer 3[/TD]
[TD]Product B[/TD]
[TD]4%[/TD]
[TD]4.5%[/TD]
[TD]3.7%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]etc.[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Is it possible to add discount column to Table 1 and look up appropriate discount based on month (column) and customer/product combination (row)?

Or maybe is it possible that each customer has his own discount table which contains only 3 columns (month, product and discount), and then "lookup" will find corresponding discount in each customers discount table (table names equals customer names)?

I hope I managed to wrote it clear enough and thank you in advance for your replies!

 

[MD610]

Power Pivot generally works best on unpivoted data, so the format of Table 2 is not ideal for this.

If you can unpivot your various Month columns in Table 2 then the lookup you desire becomes pretty simple using the function =LOOKUPVALUE() in Power Pivot.

Unpivoting your data can be done pretty easily using the relatively new Power Query add-in for excel. Ideally, you want Table 2 to look more like this:

[TABLE="width: 319"]
<tbody>[TR]
[TD]Customers
[/TD]
[TD]Products
[/TD]
[TD]Month
[/TD]
[TD]Discount
[/TD]
[/TR]
[TR]
[TD]Customer 1
[/TD]
[TD]Product A
[/TD]
[TD]7
[/TD]
[TD="align: right"]5.0%
[/TD]
[/TR]
[TR]
[TD]Customer 1
[/TD]
[TD]Product A
[/TD]
[TD]8
[/TD]
[TD="align: right"]5.5%
[/TD]
[/TR]
[TR]
[TD]Customer 1
[/TD]
[TD]Product A
[/TD]
[TD]9
[/TD]
[TD="align: right"]4.8%
[/TD]
[/TR]
[TR]
[TD]Customer 1
[/TD]
[TD]Product B
[/TD]
[TD]7
[/TD]
[TD="align: right"]7.0%
[/TD]
[/TR]
[TR]
[TD]Customer 1
[/TD]
[TD]Product B
[/TD]
[TD]8
[/TD]
[TD="align: right"]7.0%
[/TD]
[/TR]
[TR]
[TD]Customer 1
[/TD]
[TD]Product B
[/TD]
[TD]9
[/TD]
[TD="align: right"]6.3%
[/TD]
[/TR]
[TR]
[TD]Customer 1
[/TD]
[TD]Product C
[/TD]
[TD]7
[/TD]
[TD="align: right"]3.0%
[/TD]
[/TR]
[TR]
[TD]Customer 1
[/TD]
[TD]Product C
[/TD]
[TD]8
[/TD]
[TD="align: right"]3.8%
[/TD]
[/TR]
[TR]
[TD]Customer 1
[/TD]
[TD]Product C
[/TD]
[TD]9
[/TD]
[TD="align: right"]4.2%
[/TD]
[/TR]
[TR]
[TD]Customer 2
[/TD]
[TD]Product A
[/TD]
[TD]7
[/TD]
[TD="align: right"]9.0%
[/TD]
[/TR]
[TR]
[TD]Customer 2
[/TD]
[TD]Product A
[/TD]
[TD]8
[/TD]
[TD="align: right"]8.5%
[/TD]
[/TR]
[TR]
[TD]Customer 2
[/TD]
[TD]Product A
[/TD]
[TD]9
[/TD]
[TD="align: right"]9.8%
[/TD]
[/TR]
[TR]
[TD]Customer 3
[/TD]
[TD]Product A
[/TD]
[TD]7
[/TD]
[TD="align: right"]2.0%
[/TD]
[/TR]
[TR]
[TD]Customer 3
[/TD]
[TD]Product A
[/TD]
[TD]8
[/TD]
[TD="align: right"]3.0%
[/TD]
[/TR]
[TR]
[TD]Customer 3
[/TD]
[TD]Product A
[/TD]
[TD]9
[/TD]
[TD="align: right"]2.0%
[/TD]
[/TR]
[TR]
[TD]Customer 3
[/TD]
[TD]Product B
[/TD]
[TD]7
[/TD]
[TD="align: right"]4.0%
[/TD]
[/TR]
[TR]
[TD]Customer 3
[/TD]
[TD]Product B
[/TD]
[TD]8
[/TD]
[TD="align: right"]4.5%
[/TD]
[/TR]
[TR]
[TD]Customer 3
[/TD]
[TD]Product B
[/TD]
[TD]9
[/TD]
[TD="align: right"]3.7%
[/TD]
[/TR]
</tbody>[/TABLE]

Once the data looks like that, the syntax for a Calculated Column in table1 would be something like this:

=LOOKUPVALUE(Table2[Discount],
             Table2[Customers], Table1[Customer], 
             Table2[Products], Table1[Product],
             Table2[Month], Table1[Month]
   )

 

[Kaasiic]

Thank you, Mike!

I didn't know about Power Query add-in until now. It really does the job and even more! Once again - thanks!

But I still wonder is it possible to leave table reference part in some Power Pivot function dynamic?
For example if customers company name is "Samsung" Power Pivot based on this value searches further for required value based on different criteria in table that is also called "Samsung".
Could be handy in different situations.