Forum Discussion
Help modeling situation "sample"
Buenas tardes, quisiera consultar por algo muy especifico pero que quizás es mas simple de lo que parece.
En resumen tengo 2 tablas:
1. Donde se van registrando los pedidos de venta y datos generales de esta, en especifico los kilos entregados
2. Se van registrando los costos asociados a cada pedido de venta, para 1 pedido de venta pueden haber muchos costos asociados dependiendo del desglose que uno quiera utilizar, en mi caso generalmente para cada pedido de venta hay 3 costos, costo terrestre, costo aereo, y costo documental(asociado al transporte aereo)
Aqui van mis dos tablas de ejemplo:
Tabla 1:
| PV | KILOS | DESTINO | FECHA | SMEANA |
| 1 | 5000 | CHINA | 44207 | 2 |
| 2 | 2000 | USA | 44207 | 2 |
| 3 | 3000 | USA | 44208 | 2 |
| 4 | 2500 | CHINA | 44208 | 2 |
| 5 | 8000 | PERU | 44209 | 2 |
Tabla 2:
| PV | TIPO TRANSPORTE | EMPRESA TRANSPORTE | TIPO COSTO | COSTO |
| 1 | 103 | GLA | TRANSPORTE | 800 |
| 2 | 103 | GLA | TRANSPORTE | 200 |
| 1 | 104 | LATAM | TRANSPORTE | 3000 |
| 2 | 104 | QATAR | TRANSPORTE | 2500 |
| 1 | 104 | ARTURO PEREZ | DOCUMENTAL | 300 |
| 2 | 104 | ARTURO PEREZ | DOCUMENTAL | 300 |
| 3 | 103 | VASQUEZ | TRANSPORTE | 600 |
| 4 | 103 | VASQUEZ | TRANSPORTE | 300 |
| 3 | 104 | QATAR | TRANSPORTE | 3000 |
| 4 | 104 | LATAM | TRANSPORTE | 2500 |
| 3 | 104 | ARTURO PEREZ | DOCUMENTAL | 300 |
| 4 | 104 | ARTURO PEREZ | DOCUMENTAL | 300 |
| 5 | 103 | GLA | TRANSPORTE | 800 |
| 5 | 104 | LATAM | TRANSPORTE | 6000 |
| 5 | 104 | ARTURO PEREZ | DOCUMENTAL | 300 |
La medida que quiero en general es la que me calcule el costo por kilo ($/kg), y que pueda utilizarla para los distintos filtros de la tabla de costos, por ejemplo, querer ver por transporte tipo 103, sus rate,
| EMPRESA TRANSPORTE | Suma de COSTO | kilos2 | $/kg |
| CONTOPSA | 800 | 20500 | 0,04 |
| GLA | 1000 | 20500 | 0,05 |
| VASQUEZ | 900 | 20500 | 0,04 |
| Total general | 2700 | 20500 | 0,13 |
No logro entender porque para cada tranposrte dice que son 20500 kilos, y por lo tanto calcula mal el rate, debería ser para Contopsa sólo 8000 kilos (ya que el transporte contopsa sólo lleva la PV 5), para Vasquez 5500 kilos ya que esta solo lleva la PV 3 y 4,
Me podrían ayudar a entender que estoy haciendo mal? me falta crear otra tabla para unir ambas?, estoy conectando ambas tablas sólo por la PV
Muchas gracias, mi cel es 56981598377, estoy disponible para recibir comentarios por wsp si gustan.
Hi, Anonymous
According to your sample data and the expected result, I found that there occurs a little problem in your sample data. The value I circled should be “CONTOPSA”, right?
Then I created a One to many, Both filter on these two tables, like this:
And I created a Matrix and a Slicer and place them like this:
And I guess this is what you want.
You can download my test pbix file here
If this result is not what you want, you can post some sample data(without sensitive data) and your expected result.
How to Get Your Question Answered Quickly
Best Regards,
Community Support Team _Robert Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
- MFelix
Super User
Hi Anonymous you need to create a relationship between both table by the PV that way you will get expected result.
In the example data you give things don't exatly match with the information you provide but has you can see the quantities match and therefore the price per kg.
- v-robertq-msft
Community Support
Hi, Anonymous
According to your sample data and the expected result, I found that there occurs a little problem in your sample data. The value I circled should be “CONTOPSA”, right?
Then I created a One to many, Both filter on these two tables, like this:
And I created a Matrix and a Slicer and place them like this:
And I guess this is what you want.
You can download my test pbix file here
If this result is not what you want, you can post some sample data(without sensitive data) and your expected result.
How to Get Your Question Answered Quickly
Best Regards,
Community Support Team _Robert Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
