Jul 06 2021 11:21 PM
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.
Jul 10 2021 07:24 PM
Jul 11 2021 05:46 PM
Jul 11 2021 06:02 PM
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.
Jul 11 2021 08:56 PM
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.
Jul 11 2021 09:15 PM - edited Jul 11 2021 09:21 PM
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: