Using CONTAINS to find data dependent on row/column context of a pivot table

[masplin]

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

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?

=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]))
                                              )

 

[Matt Allington]

A pivot table is an aggregation tool. If you set up your pivot like you go have suggested, then you need to write a measure that will aggregate the values in the GPTV column. You could count the values

=countrows(Depot_GP_Type)

or sum the values

=sum(Depot_GP_Type[GPTV])

the values approach you describe can work on text columns when there is only a single value as long as you protect for the case where there is more than 1 value by wrapping the entire formula in an IF(HASONEVALUE( construct. But if you know there is only 1 value, then why not just use SUM.

 

[masplin]

Hi Matt

I change the code but still says multiple values

CALCULATE(
                                  SUM(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]))
                                              )
                                             
                                 )

I suppose the question is whether my CONTAIN clauses is doing what I expect. The intention is to filter the table Depot_GP_Type to give me a unique row that has the Depot Abbrev = Depot[Depot] on rows and the Type[Type] = type on the columns.

 

[Matt Allington]

This is the full formula

=SUM(Depot_GP_Type[GPTV])

 

[masplin]

Ah I see. The reason I was trying to do this was actually a more complicated calculation and this was a stepping stone. I have another table with contracts per depot per type. I want to multiple the contract by this GPTV to get a sales value. Using Sum(GPTV) works correctly for each type but the total is completely wrong as it is summing the GPTV for all type and then multiplying by the total contracts so comes out miles too high.

I was therefore trying to construct some kind of SUMX calculation that multiplied each contract by the related GPTV and then summed the result. For example the bottom row should have a total 679 not 4862 i.e. the sum of the 5 columns. The [Estimated Bookings] is a measure not a table column, based off data in a different table.

	Type
Depot	MOT	Service	Combi	Other	Brand	Grand Total
Estimated Bookings
BAS	13	4	2		1	22
BRI	14	10	2	0	6	33
CAR	9	10	1		7	29
KET	8	4	0	0	2	16
LEE	4	8	1		10	25
LIV	27	23	3	0	10	65
MIL	7	4	1		2	15
NOR	13	9	3	1	22	49
NOT	8	10	2	0	9	31
STE	5	7	0		6	20
STO	3	5	1	0	4	15
WAR	19	18	6		7	51
WEN	2	15	2	0	8	28
Sum of GPTV
BAS	£30	£23	£79	£50	£39	£221
BRI	£52	£119	£149	£50	£93	£463
CAR	£61	£128	£164	£50	£109	£512
KET	£49	£93	£115	£50	£66	£373
LEE	£27	£22	£71	£50	£31	£201
LIV	£35	£28	£88	£50	£52	£253
MIL	£40	£102	£147	£50	£70	£409
NOR	£54	£115	£131	£50	£89	£439
NOT	£43	£116	£166	£50	£103	£478
STE	£47	£86	£132	£50	£83	£398
STO	£63	£96	£126	£50	£96	£431
WAR	£58	£40	£116	£50	£72	£336
WEN	£43	£114	£156	£50	£98	£461
Total Estimated Bookings	127	135	29	2	104	399
Total Sum of GPTV	£602	£1,082	£1,640	£650	£1,001	£4,975
BAS	390	92	158	0	39	4862

<tbody>
</tbody>

I tried this sort of thing which is correct for individual cells but all the totals are completely wrong as using the SUM of GPTV for all types or all depot rather than summing the components. I know SUMX would work if I just had 2 columns of data in a table so thought maybe the ADDCOLUMNS would mimic this.

=SUMX(
              ADDCOLUMNS(
                                        PPC,
                                        "Type GPTV",SUM(Depot_GP_Type[GPTV])
                                        ),
              PPC[Estimated Bookings]*[Type GPTV]
              )

 

[Matt Allington]

Power Pivot is a system of tables and relationships that interoperate together. It is very difficult (maybe impossible) to give you a correct formula without seeing the underlying table structure. have a read of this blog post I wrote and it may help you. Otherwise, please post a picture of the diagram view of the data model and indicate which tables have the relevant columns you are trying to use.
When to use SUM vs SUMX in DAX

 

[akice]

ADDCOLUMNS is an iterator but doesn't do automatic context transition. The SUM(Depot_GP_Type[GPTV]) will Sum the whole table (as you noted), not the ones related to each row of PPC. You need to wrap it in a CALCULATE to get transition.

CALCULATE ( SUM ( Depot_GP_Type[GPTV] )).