I have a table of values Depot_GP_Type. Depot Abbrev is related to a Table "Depot" and Type is related to a table "Type" that contain just the unique values of each
<tbody>
</tbody>
i have a pivot table with rows Depot[Depot] and columns Type[Type]. I am trying to work out how to select the appropriate number form the first table and get it in the right cell. for example
MOT Service
BAS 27 23
BRI 41
i thought this might work although i'm not 100% sure (obviously as it doesn't!!) how contains works as trying to use the pivot table context as a filter to find the values. however says it is returning multiple values. Am I even close?
Depot Abbrev | Type | GPTV |
BAS | Other | £50 |
BAS | MOT | £27 |
BAS | Combi | £75 |
BAS | Service | £23 |
BAS | Brand | £34 |
BRI | Other | £50 |
BRI | MOT | £41 |
<tbody>
</tbody>
i have a pivot table with rows Depot[Depot] and columns Type[Type]. I am trying to work out how to select the appropriate number form the first table and get it in the right cell. for example
MOT Service
BAS 27 23
BRI 41
i thought this might work although i'm not 100% sure (obviously as it doesn't!!) how contains works as trying to use the pivot table context as a filter to find the values. however says it is returning multiple values. Am I even close?
Code:
=CALCULATE( VALUES(Depot_GP_Type[GPTV]),
FILTER(
Depot_GP_Type,
CONTAINS( Depot_GP_Type, Depot_GP_Type[Depot Abbrev],VALUES(Depot[Depot])) &&
CONTAINS( Depot_GP_Type, Depot_GP_Type[Type],VALUES('Type'[Type]))
)