Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
powerBIpeon
Helper II
Helper II

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 CodeModule 1Module 2Module 3Module 4
1234250  250
567875757575
9101175757575
121314250  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!

1 ACCEPTED SOLUTION
v-eachen-msft
Community Support
Community Support

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.

 

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.

View solution in original post

3 REPLIES 3
v-eachen-msft
Community Support
Community Support

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.

 

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.
sturlaws
Resident Rockstar
Resident Rockstar

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

@sturlaws 

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.