Forum Discussion
Helper IIIProblem calculating PROFIT with header and detail Fact Tables.
Hi ContabilidadBI,
I took Facturas[Dto] as unit value. You could try this formula.
net profit € = SUM ( 'FactLineas'[Ingresos] ) - SUMX ( FactLineas, FactLineas[Cantidad] * FactLineas[PrecioCoste] ) - SUM ( 'Facturas'[Dto] ) - SUM ( 'Rappel'[Rappel] )profit % = DIVIDE ( [net profit €], SUM ( 'FactLineas'[Ingresos] ), 0 )
Best Regards!
Dale
Microsoft EmployeeHi ContabilidadBI,
The records in the header table look like unique. So you could try the function "lookupvalue". The formula could be:
profit € =
SUMX (
FactLineas;
FactLineas[Cantidad]
* (
FactLineas[PrecioCoste]
- LOOKUPVALUE ( Header[Dto]; Header[CodFactura]; 'FactLineas'[CodFactura] )
)
)How does the last discount table connect with the other tables? Maybe you could try "lookupvalue" too. If you still have any question, please post the relationships and related data in the text mode.
BTW, if you post your data in the text mode, I think many people would be glad to help.
Best Regards!
Dale
Helper IIIThanks for your answer Dale.
Can you please inform me how can I put the data in text form?
Yes, CodFactura in the header table, is the primary key in the relationship with the detail table, so the values are unique. The last discount table has 2 relationships, client key that goes to the CLIENTS table and date that goes to the DATE table.
Thank you so much!!
- v-jiascu-msft
Hi ContabilidadBI,
What are the relationships among the tables "clients", "header" and "detail"? This would the key point how we can deal with the discounts. Could you please post snapshot of the relationship?
There are two ways to post data in the text mode.
1. Copy the data (in a table most time) to Notepad, then copy the data from Notepad and paste here;
2. Copy the data, open "insert code" and paste.
Best Regards!
Dale
- ContabilidadBI
Hi v-jiascu-msft, thanks again for your help.
Here you can see the relationships between header (Facturas), detail (Factlineas) and Clients (Clientes):
Header and detail are related by CodFactura (being the primary key in the header), and clients is related to the header by CodCliente which is the client key. And here you can see the relationship between clients and the last discount table (Rappel):
I will put the tables in text form in the following post.
- ContabilidadBI
Header:
SerieFac NumFac Fecha Estado CodCliente Total Neto Dto Ingresos IVA RE CodFactura HOSTELERIA 170001 04/01/2017 Cobrada 304 95,70 87 0 87,00 8,7 0 1-170001 HOSTELERIA 170002 13/01/2017 Pendiente 240 69,51 61 0 61,00 8,51 0 1-170002 HOSTELERIA 170003 18/01/2017 Cobrada 317 19,14 17,4 0 17,40 1,74 0 1-170003 HOSTELERIA 170004 19/01/2017 Cobrada 900 18,51 16,24 0 16,24 2,27 0 1-170004 HOSTELERIA 170005 25/01/2017 Cobrada 207 355,64 340,33 17,02 323,31 32,33 0 1-170005 HOSTELERIA 170006 31/01/2017 Cobrada 368 450,00 409,09 0 409,09 40,91 0 1-170006 HOSTELERIA 170007 31/01/2017 Cobrada 4 192,43 175,11 0 175,11 17,32 0 1-170007 HOSTELERIA 170008 31/01/2017 Cobrada 8 11.392,92 10728,74 371,54 10.357,20 1035,72 0 1-170008 HOSTELERIA 170009 31/01/2017 Cobrada 20 154,28 140,25 0 140,25 14,03 0 1-170009 HOSTELERIA 170010 31/01/2017 Cobrada 205 12.500,21 11825,17 461,34 11.363,83 1136,38 0 1-170010 HOSTELERIA 170011 31/01/2017 Cobrada 246 476,31 433,01 0 433,01 43,3 0 1-170011 HOSTELERIA 170012 31/01/2017 Cobrada 247 8.880,01 8233,3 160,56 8.072,74 807,27 0 1-170012 HOSTELERIA 170013 31/01/2017 Cobrada 294 875,28 796,38 0 796,38 78,9 0 1-170013 HOSTELERIA 170014 31/01/2017 Cobrada 318 221,58 201,44 0 201,44 20,14 0 1-170014 HOSTELERIA 170015 31/01/2017 Cobrada 327 924,81 840,74 0 840,74 84,07 0 1-170015 HOSTELERIA 170016 31/01/2017 Cobrada 328 110,22 100,2 0 100,20 10,02 0 1-170016 Detail:
SerieFac NumFac CodArticulo Cantidad Precio Ingresos PrecioCoste CodFactura CosteMinorado 1 170010 00012 12,39 9,2 € 113,94 € 7,87 € 1-170010 7,38 1 170010 00012 6,17 9,2 € 56,72 € 7,87 € 1-170010 7,38 1 170010 00012 8,67 9,2 € 79,72 € 7,87 € 1-170010 7,38 1 170010 00012 13,47 9,2 € 123,92 € 7,87 € 1-170010 7,38 1 170010 00012 8,79 9,2 € 80,87 € 7,87 € 1-170010 7,38 1 170010 00012 5,83 9,2 € 53,59 € 7,87 € 1-170010 7,38 1 170010 00012 20,74 9,2 € 190,81 € 7,87 € 1-170010 7,38 1 170010 00012 5,67 9,2 € 52,16 € 7,87 € 1-170010 7,38 1 170008 00012 3,93 9,2 € 36,16 € 7,87 € 1-170008 7,38 1 170008 00012 4,80 9,2 € 44,11 € 7,87 € 1-170008 7,38 1 170008 00012 11,96 9,2 € 110,03 € 7,87 € 1-170008 7,38 1 170008 00012 4,61 9,2 € 42,41 € 7,87 € 1-170008 7,38 1 170008 00012 3,83 9,2 € 35,24 € 7,87 € 1-170008 7,38 1 170008 00012 5,59 9,2 € 51,38 € 7,87 € 1-170008 7,38 1 170012 00012 10,12 9,2 € 93,10 € 7,87 € 1-170012 7,38 1 170012 00012 9,90 9,2 € 91,03 € 7,87 € 1-170012 7,38 1 170012 00012 5,88 9,2 € 54,10 € 7,87 € 1-170012 7,38 1 170012 00012 5,10 9,2 € 46,92 € 7,87 € 1-170012 7,38 1 170012 00012 12,97 9,2 € 119,28 € 7,87 € 1-170012 7,38 Last Discount (Rappel):
Fecha CodCliente Rappel 31/01/2017 8 513,962 31/01/2017 205 566,929 31/01/2017 247 396,897 31/01/2017 330 341,884 31/01/2017 367 125,912 31/01/2017 1000 125,539 28/02/2017 8 481,779 28/02/2017 205 613,189 28/02/2017 247 453,64 28/02/2017 330 532,904 28/02/2017 367 288,054 28/02/2017 1000 150,237 31/03/2017 8 485,963 31/03/2017 205 647,042 31/03/2017 247 475,174 31/03/2017 330 562,678 31/03/2017 367 237,343 31/03/2017 1000 219,996 Thanks again Dale, these aren't the full tables because they are too long but I guess this is enough.