topic Re: Ratio of two sums in DAX Commands and Tips

Ratio of two sums

Anonymous — Mon, 17 Oct 2022 08:08:19 GMT

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!

Re: Ratio of two sums

amitchandak — Mon, 17 Oct 2022 08:29:04 GMT

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.

Re: Ratio of two sums

Anonymous — Tue, 18 Oct 2022 09:35:14 GMT

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.