Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
Solved! Go to 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.
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.
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
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |