Can we use match formula in pivot table's calculated fields?

[Joseph_Hung]

Hello Community,

I'm trying to create a calculated field in a pivot table and there is one formula as below.

This formula works fine in my excel sheet, but I can't create one in pivot table as a formula. Is it because of the match, offset or column functions that prevent me from doing so? 

=MATCH(O4,SUBTOTAL(9,OFFSET(Y4,,,,COLUMN(Y4:AK4)-COLUMN(Y4)+1)))

Thanks a lot.

[#2]

i am assuming you are using data model and power pivot?

[#3]

Hi Yea_So - No, I'm actually using the basic pivot table. Any ideas why...?

[#4]

Hi, @Joseph_Hung,

A pivot table is used to organize, summarize and aggregate dataset to answer analysis questions.

so if you have to use a match function, it tells me that either 1. you are using structured tables in a data model to summarize, aggregate your dataset to answer analysis questions.  If you are using match function, then you are still in the dataset prepping stage and should not be creating the pivot table until you are done looking up  values to match all the information from other structured excel defined tables.

[#5]

Hi @Yea_So 

I think the problem might be the Column function here because I put the formula in calculated field and try to click "ADD", but it bounce back to the column function marked as grey below. 

[#6]

Hi @Joseph_Hung,

I will show you how to do a lookup in a data model.  You cannot do a lookup in a basic/classic pivot table, it has to be done in excel (data prep).

here is a sample of power pivot I am working on (playing with):

the behind the scenes of this power pivot/data model is the following:

I added the excel structured tables into the data model, then created a relationship to each table:

by doing that, you can do a lookup against the tables with relationships.

this is the Trucks table

and this is the job details table (transaction table)

if you notice the truck number is included in the transaction table

and by that value i can do a lookup to the trucks table by using the related function to get the truck type, if you look at the formula bar, that

=RELATED(RD_Trucks[Truck]) formula is how you do a lookup into another table within the data model:

if you look at the heading of the truck type the color is black which tells you that the values in the column a lookup is being brought over to the transaction table

watch this video about data modeling:

https://youtu.be/e-CFYi52gpc