The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
Hi everyone,
I'm trying to calcualte the ratio between two sums. My dataset is built as follows:
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!
Solved! Go to 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.
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.
@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.
User | Count |
---|---|
15 | |
8 | |
6 | |
6 | |
6 |
User | Count |
---|---|
24 | |
14 | |
13 | |
8 | |
8 |