How to calculate unit cost on basis of conditions

[sagniksince89]

Hi All,

I have a column called Line_Item which tells if its shipped_volume or shipped_cost or delivered_volume or delivered_cost.
Next column is Cost where it has volume/cost data as per the line_item.
I need to figure out the unit cost -
Formula would be -
if line_item = ‘Shipped_cost’ then sum(shipped_cost)/sum(shipped_volume)
if line_item = ‘Delivered_cost’ then sum(Delivered_cost)/sum(Delivered_volume)
For, the rows where its Delivered_volume or Shipped_volume, unit cost would be null.
Can anyone kindly help how to achieve it in quicksight?

Line_Item	Cost	Unit Cost
Shipped_volume	20
Shipped_cost	40	2
Delivered_volume	25
Delivered_cost	75	3

Thanks.

[Koushik_Muthanna]

@sagniksince89 ,

I have seen this type of data in orders where you are calculating costs for each order.
For an item, you have different line items and I think you should pivot your data .

Shipped_volume	Shipped_cost	Delivered_volume	Delivered_cost
20	40	25	75

Otherwise

Regards,
Koushik