Allocation based off different table

[powerBIpeon]

Hi All,

I'm trying to allocate out a specfic dollar amount based on a table I have built. I have a allocation table that will include percentages. As a order comes in I want it to reference that table and allocate it to the specfic modules. Here's is how the current relationship is built.

Relationship 

https://www.dropbox.com/s/sgnsqfkj5423z7a/Capture.PNG?dl=0

Results Table

Item Code	Module 1	Module 2	Module 3	Module 4
1234	250			250
5678	75	75	75	75
91011	75	75	75	75
121314	250			250

Link to PowerBI file

https://www.dropbox.com/s/zknbjjdevnqneoz/allocation%20example.pbix?dl=0

I did create a calculated column, but its limited to only calculating 1 field. I'm working with a large data set so that is not ideal

Thanks in advance!

[v-eachen-msft · Accepted solution]

Hi @powerBIpeon ,

You could use RELATED() function to do it like the following DAX:

Column 1 =
VAR a =
    RELATED ( 'allocation table'[Module 1] ) * Quotes[Price]
RETURN
    SWITCH ( TRUE (), a = 0, BLANK (), a )

Here is my test file for your reference.

[sturlaws]

Hi @powerBIpeon ,

what is you desired outcome? A table in your model with the item code and the price allocated to the modules? Or do you want to visualize it in a table vizualization?

Cheers,
Sturla

[powerBIpeon]

@sturlaws 

table with item code and the price allocated to the modules.