Allocating costs based on a list of allocation %ages

[dazfoz]

Hi, 

First time poster, and quite a new user so please be gentle on me :).

Im trying to create a cost matrix in PBI, but struggling to calculate the answer.

I have a table 'COS-Allocations' which contains three columns, [COS],[PRODUCT] & [ALLOCATION].  there is repetition of each COS and each product throughout the 300 rows (example below)

[COS]	[PRODUCT]	[ALLOCATION]
COS 1	PRODUCT 1	10%
COS 1	PRODUCT 2	30%
COS 1	PRODUCT 3	null
COS 1	PRODUCT 4	null
COS 1	PRODUCT 5	60%
COS 2	PRODUCT 1	30%
COS 2	PRODUCT 2	40%
COS 2	PRODUCT 3	null
COS 2	PRODUCT 4	30%
COS 2	PRODUCT 5	null
COS 3	PRODUCT 1	null
COS 3	PRODUCT 2	10%
COS 3	PRODUCT 3	10%
COS 3	PRODUCT 4	30%
COS 3	PRODUCT 5	50%

Im using a Matrix and putting [PRODUCT] in the column, [COS] in the rows, and for the value im using the following measure;

sum('TABLE CONTAINING COS VALUES'[COS]) * sum('COS-Allocations[ALLOCATION])

My expection is that the value for each COS will reference row 1 & row 2 in the table and multiply by the percentage that is given, but Im just getting a return in every single matrix point that has an allocation %.  Hope this makes sense.....

Im pulling my Hair out, I know that its something to do with not properly filtering on the [COS] but i just cant figure it out.

Thanks in advance

[FreemanZ]

hi @dazfoz 

An excellent first post already, i would say. But still something missing, e.g. how are the two mentioned tables related? what do you mean by row1 and row2?

[dazfoz]

FreemanZ, 

Thanks for your reply, sorry I wasnt very clear, row1 I should have said column 1or [COS] & Row 2 should have said column 2 or [PRODUCT]. 

Based on your question.......I now realise that the 'TABLE CONTAINING COS VALUES'[COS] isnt linked to 'COS-Allocations[COS]...

I dont know how to acheive this, since the [COS] in 'TABLE CONTAINING COS VALUES'[COS] are running along the top of the table (ROW1), with dates down the left hand side (ColumnA), but the [COS] in  'COS-Allocations[COS] are in (Column A) as per my example table posted in my OP.