Linking tables with multiple value entries

[awolf88]

Hi geniusses!

Here's a bit of a tricky situation:

I have two tables to deal with, one for Budget - one for Sales. 

Within my budget, I have customers ordering the same product for multiple projects. They are assigned with projected QTYs each. 

The difficulty comes within my Sales table: When customers place orders, I do not have the information of which Project the items are purchased for, hence having trouble allocating them accordingly to my budget. 

The solution my team came up with was adding additonal rows to the Budget calculation: 

- one combining Customer&Product ID

- one calculating the total max projected by Customer/ProductID = sumif([Customer/Prod];[@[Customer/Prod]];[projected Qty])
- one creating a multiplication factor as a result that weighs on quantity projected: "mult. Factor" = if([total max]]>0;[@projected Qty]]/[@[total max]];0) --- in our example row 1 (Apple1516) would have a larger factor than row 4 (Apple1516) as it's projected qty is larger than the other. 

Now my ultimate goal is to virtually relate those two tables with one another (via the "customer/prod" column I would assume?) and then multiplying the ordered qty with the multiplication factor to distribute the quantities respectively if that makes sense. 

So in our example, the Apple1516 order of 100pcs would be allocated by *0,625 towards the project "Phone", while the remaining (100*0,375) would allocate towards project "iPad". 

I've come pretty far with DAX coding measures in order to achieve the above but seem to be struggling with the last step of multiplying with the factor. The actual numbers seem correct assigned by it's values, but the subtotals/grand total seem to be going through the roof.

I would seriously appreciate some input on how to solve this. 

Cheers

[Anonymous · Accepted solution]

Hi  @awolf88 ,

Here are the steps you can follow：

1. Create measure.

Total_m Orders total *factor =
var _table=SUMMARIZE('Budget','Budget'[Customer],"_value",[m Orders total *factor])
return
IF(HASONEVALUE('Budget'[Customer]),[m Orders total *factor],SUMX( _table,[_value]))

2. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[awolf88]

Here's a link to the demo-file also:

https://wetransfer.com/downloads/45264941aaf763a64066883fab1f166420220226082240/7de5ffcd014dc8b3c5bd...