Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
---|---|
98 | |
90 | |
82 | |
73 | |
67 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
67 |