Hello Guys!
I hope someone can help out, I’m trying to do a Rule of Three to find the Shipping Cost Per Product.
= ( Nett Weight * Shipping and Handling) / Nett Shiping Weight
= ( 0.500 g * 927.64 $ ) / 1213.28 Kg
The results should be as follow :
To showcase the questions, I’m Highlighting one product.
The formula that I’m using is as follow, but the result I get is 38.65 $
I attached the PBIX file for support.
Thanks guys!
Yeriel
Example Data.pbix (245.5 KB)
Hi @Yeriel
Please check your DAX logic
Nettshippingweight gives 12(instead of 1213.28) in your logic that’s we are getting 38.65
Hi @Rajesh
To get the total Cost per Product, I need to take into consideration the Nett Shipping Weight of Shipping ID 9, hence the 1213.28 Kg.
The formula needs to be as follows:
= ( Products Nett Weight * Shipping and Handling) / Net Shipping Weight
= ( 0.500 g * $ 927.64 ) / 1213.28 Kg
= $ 0.38
Hi @Yeriel,
Create a Nett Shipping Weight Total measure, for example.
Nett Shipping Weight Total =
VAR ShipID = SELECTEDVALUE( Shipping[Shipping ID] )
RETURN
CALCULATE( [Nett Shipping Weight],
FILTER( ALL( Buyings ), Buyings[Shipping ID] = ShipID )
)
Next in your Shipping Cost per Product measure point the VAR NettShippingWeight to the new [Nett Shipping Weight Total] measure
Let me know if this is helpful.
Thanks a lot @Melissa!!!
The formula was really helpful 
I was not taking into consideration the Shipping [Shipping ID] to obtain the Total Weight per Order, now a have a better understanding of a better process.
Cheers!!
Hi @Melissa
Do you think there’s a way to make the formula Static?
Because when I remove the Shipping ID from the table, I can’t see any data.
Thanks for the support!
Yeriel.
Well…
you could hard code the ShipID instead of using SELECTEDVALUE but be very explicit in your measure name in that case - personally I don’t like hard coding because ‘things have a tendency to change’.
However if your goal is to not show the ShipID in a matrix, disable wrap in the settings and change the column width so you can’t see it. This will bring in the context without visually disturbing the report.
and setting the font colour to the same as you background always helps …
Greg
Hi @Melissa, I removed the Shipping ID from the table and re-do the measures.
I found a solution for the Nett Shipping Weight Total without the Shipping ID, is not so elegant but I think it works:
Nett Shipping Weight Total 2 =
CALCULATE( [Nett Shipping Weight],
FILTER( ALL( Shipping[Shipping ID] ), [Nett Shipping Weight] ),
FILTER( ALL( Buyings ), [Nett Shipping Weight] > 0 ) )
I’m having a hard time with the VAR ShippingandHandling though.
I cannot make it work.
The variable needs, to sum up, the ( Shipping[Shipping Cost] + Shipping[Handling Fee] ) for every Shipping ID without having the Shipping ID in the table.
For example, all the Subproducts that ends with 9 needs to be $927.64
Nice one @Yeriel
Give this a try, will you.
Shipping and Handling 2 =
VAR vTable = SUMMARIZE( Subproducts, Subproducts[Product ID], Shipping[Handling Fee], Shipping[Shipping Cost] )
RETURN
SUMX(vTable, [Shipping Cost] + [Handling Fee] )
I hope this is helpful.
Glad I could help.
