topic Allocating costs based on a list of allocation %ages in DAX Commands and Tips

Allocating costs based on a list of allocation %ages

dazfoz — Thu, 30 Mar 2023 16:11:53 GMT

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

Re: Allocating costs based on a list of allocation %ages

FreemanZ — Fri, 31 Mar 2023 02:13:08 GMT

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?

Re: Allocating costs based on a list of allocation %ages

dazfoz — Fri, 31 Mar 2023 09:18:44 GMT

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.