Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
100 | |
88 | |
68 | |
61 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |