Helps sort BD and create data model

[felipekrebs]

I need help creating a data model that fits what I need, I'm creating it in power pivot at the moment, because in my company it's not allowed to install power bi, so if you could help me with what's available in power pivot it would be ideal. (although if there is no other, I am told that it is only possible in power bi, to talk to the people in charge)

I need to keep track of the costs ($/kg) of exports of a product, by recording logistical costs. So far I have 2 tables (which you can advise me to make and undo whole if necessary). I put examples with fictitious values for understanding

It is a company that sells products from different points of origin (since it has several process plants), and makes sales in different modalities, air or maritimo, if it is airy there are involved minimum 2 costs, the cost of taking the product to the airport and the cost of bringing it airily to the destination (there could be more costs, for example there could be an overstimal to be late at the airport) , and the marsh, where there are also at least 2 costs, the cost of taking the product to the port, and the cost of bringing the product maritimely to the destination, (there could also be over costs). (and in cases a single cost is agreed with the shipping company, from the origin to the port of destination, i.e. 1 cost only that includes the land and sea cost).

Table 1: I explain in short the meaning of each field.

PV (sales order): "UNICO" field in this table, each sale has its unique sales number, which is this.

Final destination: final destination of sale

Customer: Customer being sold

Kilos: How many kilos are involved in this sale (very important)

boxes: in how many boxes this order is distributed (not very important)

Origin: Where the order departs from

Customs agency: a company that manages the international exit of the product

Departure: Whether it's by port or airport

Location: Identification of which specific port or airport.

Travel company: an air or port company that would transfer the product

PV	final destination	Client	Kilos	Boxes	Origin	Customs Agency	exit	Place	Travel company
1	Colombia	C1	5000	500	PM	EM1	Airport	AE1	LAT
2	china	CH1	2000	100	PM	EM1	Airport	AE2	LAT
3	Japan	JP1	10000	200	PM	EM1	Airport	AE1	LAT
4	USA	USA1	5000	100	UCO	EM2	Port	P1	MSC
5	USA	USA2	5000	100	UCO	EM2	Port	P2	MSC
6	USA	USA2	5000	100	UCO	EM2	Port	P2	MSC

Then we have table 2 and very important which is the one that keeps track of all costs:

PV: Each PV can have several associated costs, for example PV 1 has the cost of bringing the product on land to the airport, then the cost of taking it to the destination country and also has an extra cost for being late for example. (for land transport there are also different companies that can transfer the product).

Concept indicates whether it is a normal cost or if it is an extra cost to what is normally paid.

Cost, it's the cost of the service.

PV	Transport	Trans company	Concept	Cost
1	Land	A	Normal	500
1	Plane	LAT	Normal	100
1	Land	LAT	extra	50
2	Land	B	Normal	400
2	Plane	LAT	Normal	100
3	Land	A	Normal	400
3	Plane	LAT	Normal	100
4	Land+Sea	MSC	Normal	1000
4	Land+Sea	MSC	extra	100
5	Land+Sea	MSC	Normal	800
5	Land+Sea	MSC	Normal	700

What I need is to be able to make a dynamic table that can create a calculated field of $/kg, where I can handle as I please all the parameters, if I want to see the costs per kilo ($/kg) either by destination, by transport (air, maritimo, terrestrial), by PV, by transport company, by customer,

The problem is that by putting some fields into the TD (e.g. kilos) I repeat the overall total in all fields, and tells me that it requires more connection, that kind of problems. Consider that the database might have thousands of data.

As I said, if you want to recommend a completely different way of carrying the data, I ask you to change it, or suggest adding new fields (in fact I didn't say it, but I need to add the date, for example take ground cost control per week, per month, etc).).

If you can I am also available for advice via wsp or meet (+569 81598377)

I'd really appreciate your help.

[Anonymous]

Hi @felipekrebs 

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your table ,the result your want and your problem or share me with your pbix file from your Onedrive for Business.

Best Regards,

Rico Zhou

[Anonymous]

Hi @felipekrebs 

I build two table like yours to have a test. If you just want to calculate the cost per kilo and you build relationships between two tables by PV column.

You may try this measure:

Measure = SUM(Table2[Cost])/SUM(Table1[Kilos])

Result:

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.