Ratio of two sums

[Anonymous]

Hi everyone, 

I'm trying to calcualte the ratio between two sums. My dataset is built as follows:

• a table with all the Inventory Fulfillments (associated to Sales Orders, there can be more than one IF for each SO), to each IF is associated a shipping cost;
• a table with all Sales Orders, each line of the table contains an item in the sales order and the relative amount (€) ordered, it's possible to obtain the total ordered value of the SO by summing all the lines of the table, grouping by SO;

I need to calculate, for each SO, the ratio between the shipping cost and the total value of the order, but I'm having trouble doing so. 

Can you help me? I can share my initial solution nut it doesn't work at all.

Thanks!

[Anonymous · Accepted solution]

Hi @Anonymous ,

Here I create a sample to have a test.

Table1:

Table2:

DimSo:

DimSO = VALUES(Table2[Sales Orders])

Relationship:

Measure:

Rate = 
VAR _RelativeAmount = CALCULATE(SUM(Table2[Relative Amount]))
VAR _ShippingCost = CALCULATE(SUM(Table1[Shipping Cost]))
RETURN
DIVIDE(_RelativeAmount - _ShippingCost,_ShippingCost)

Result is as below.

Best Regards,
Rico Zhou

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

[amitchandak]

@Anonymous , You can have a new column

New column in SO

Sumx(filter( Fulfillments , Fulfillments [So] = SalesOrder[SO]), Fulfillments [Qty])

refer 4 ways to copy data from one table to another
https://www.youtube.com/watch?v=Wu1mWxR23jU
https://www.youtube.com/watch?v=czNHt7UXIe8

If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.