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

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
Anonymous
Not applicable

Ratio of two sums

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!

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi @Anonymous ,

 

Here I create a sample to have a test.

Table1:

RicoZhou_0-1666085620265.png

Table2:

RicoZhou_1-1666085630035.png

DimSo:

DimSO = VALUES(Table2[Sales Orders])

Relationship:

RicoZhou_2-1666085671804.png

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.

RicoZhou_3-1666085704589.png

 

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.

 

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

Hi @Anonymous ,

 

Here I create a sample to have a test.

Table1:

RicoZhou_0-1666085620265.png

Table2:

RicoZhou_1-1666085630035.png

DimSo:

DimSO = VALUES(Table2[Sales Orders])

Relationship:

RicoZhou_2-1666085671804.png

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.

RicoZhou_3-1666085704589.png

 

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
Super User
Super User

@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.

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

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