Forum Discussion
Allocation based off different table
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!
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.
3 Replies
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,
SturlaHi 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.
