Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Voltando aqui para pedir ajuda a comunidade.
Tenho duas tabelas, uma com as compras das loja e outra com as vendas das lojas, e queria uma luz em como calcular a margem dos produtos vendidos (Valor Venda - Valor Custo)/Valor Venda
As tabelas seriam assim
TABELA VENDA PARA AS LOJAS
Data | NF | Destino | Produto | Quant | Valor Unitario Custo | Valor Total Custo |
26-Jan-17 | 27019 | Loja 1 | BE047 | 4 | 60,00 | 240,00 |
26-Jan-17 | 27019 | Loja 1 | CL001 | 5 | 50,00 | 250,00 |
26-Jan-17 | 27019 | Loja 1 | BE023 | 4 | 30,00 | 120,00 |
26-Jan-17 | 27019 | Loja 1 | CL212 | 3 | 80,00 | 240,00 |
26-Jan-17 | 27019 | Loja 1 | CL012 | 2 | 75,00 | 150,00 |
26-Jan-17 | 27019 | Loja 1 | ML012 | 7 | 90,00 | 630,00 |
01-Feb-17 | 27095 | Loja 2 | BE047 | 5 | 60,00 | 300,00 |
01-Feb-17 | 27095 | Loja 2 | CL001 | 5 | 5,00 | 25,00 |
01-Feb-17 | 27095 | Loja 2 | CL012 | 2 | 75,00 | 150,00 |
TABELA VENDA DAS LOJAS
Filial | Data Venda | NF | Produto | Quant | Valor Unitario Venda | Valor Total Venda |
Loja 2 | 09-mar-17 | 4454 | CL012 | 1 | 168,00 | 168,00 |
Loja 2 | 10-mar-17 | 4456 | BE047 | 1 | 128,00 | 128,00 |
Loja 2 | 10-mar-17 | 4456 | CL001 | 1 | 105,00 | 105,00 |
Loja 1 | 01-mar-17 | 14704 | BE047 | 1 | 128,00 | 128,00 |
Loja 1 | 01-mar-17 | 14704 | CL001 | 1 | 105,00 | 105,00 |
Loja 1 | 01-mar-17 | 14704 | CL012 | 1 | 168,00 | 168,00 |
Loja 1 | 28-mar-17 | 14825 | CL001 | 4 | 105,00 | 420,00 |
Loja 1 | 28-mar-17 | 14825 | ML012 | 4 | 199,00 | 796,00 |
Se alguem puder ajudar agradeco.
Muito obrigado
Luis
Why not join both the tables and use normal expression (Value Sale - Value Cost) / Value Sale??
The tables are already linked by "product" (Produto)
The problem is I just want the margin from the produts sold.
To pick the "Value Sale" is easy, hard is to get the respective (same products and same quantities) "Value cost"
Can you help me?
When you join with product, you get the Value Sale of same products I didn't get you why you need same quantities??
Can you please explain what you are trying to calculate??
Shiva,
Tks for your help
Let me try to explain better
Another example
Cost Table
Product | Quant | Value Cost | Total Value Cost |
Prod 10 | 10 | 100,00 | 1.000,00 |
Prod 20 | 5 | 75,00 | 375,00 |
Sold Table
Product | Quant Sold | Value Sale | Total Value Sale |
Prod 10 | 3 | 300,00 | 900,00 |
Prod 20 | 1 | 150,00 | 150,00 |
The correct margin for the sold products is
Total Value Sale = 1.050 (3 x 300 + 1 x 150)
Total Value Cost = 375 (3 x 100 + 1 x 75)
Margin = 0,6429 or 64,29%
I tried to do that in 2 ways at quik sense and get different number as bellow
(Sum([Value Sale])-Sum([Value Cost]))/Sum([Value Sale]) - I get 0,6111 or 61,11%
(Sum([Total Value Sale])-Sum([Total Value Cost]))/Sum([Total Value Sale]) - I get -0,3095 or -30,95%
Hope you can help me.
Cheers