Problem calculating PROFIT with header and detail Fact Tables.

[ContabilidadBI]

Good morning guys,

I am relatively new to Power BI and DAX and I am having a problem trying to calculate the PROFIT (and PROFIT %) with the following data model with 2 sales tables.

First the header one: this table is the invoice table and look like this:

And then the detail table, where every invoice is disaggerated in every product sold in that invoice, and look like this: (cantidad is number of units)

This 2 tables are linked with CodFactura (which is the number of invoice). And my problem is the following:

In the header table there is a discount (Dto) so if I want to calculate the net profit I need to subtract the Dto first.

If I want to just calculate the gross profit I would use this formulas:

profit € = SUMX(
                    FactLineas;
                    ([VentasBrutas] - (FactLineas[Cantidad]*FactLineas[Coste])))

profit % = DIVIDE(
                [profit €];
                SUMX(
                    FactLineas;
                    FactLineas[Cantidad]*FactLineas[Coste]);
                0    
           )

But, how can I calculate the NET PROFIT, taking into account the Discount in the header table? I only need to calculate and show this profit monthly and by client. Not products or categories or weird periods or anything else. Just the monthly profit (in € and %) by client.

To make things more complicated, there is another table with a special discount only for a few customers that looks like this:

So, Rappel is another discount that I would need to subtract too, and in this table, only a few clients appear, always the same clients, every month (CodCliente is Client Key).

Thank you so much for your help!! and sorry if my english is not very good. I am loving this software!!

[ContabilidadBI]

BTW, the header table has more columns, including date of course.

Thanks

[ContabilidadBI]

Any ideas?

To make the question simpler: how can I calculate the net margin taking into account the discount (which is in another related table)??

Thanks

[v-jiascu-msft]

Hi @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

[ContabilidadBI]

Thanks 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.

[v-jiascu-msft]

Hi @ContabilidadBI,

According to my test, "lookupvalue" would work properly. The trick could be the relationships. Please change the "cross filter direction" into "both". 

net profit € =
SUMX (
    FactLineas;
    FactLineas[Cantidad]
        * (
            FactLineas[PrecioCoste]
                - LOOKUPVALUE ( Header[Dto]; Header[CodFactura]; 'FactLineas'[CodFactura] )
        )
)
    - SUM ( 'Rappel'[Rappel] )

I attached the PBIX here: https://1drv.ms/u/s!ArTqPk2pu-BkgQM-JwmUCi2S-9zf

Best Regards!

Dale

[ContabilidadBI]

Hi @v-jiascu-msft,

Sorry for the delay, I just got the time today to try and replicate what you did for me. Unfortunately, when I try to set thoes 3 relationships as bidirectional filtering I always get this error message in one of them:

The relationship you are creating allows to filter Calendar by Clients, but Power BI Deskpot only allows 1 rute of access of filtering between the tables of a data model. Desactivate the relationships between the tables or change the filter direction. This relationship can be set as unidirectional filtering.

I was thinking that this "header and detail Sales" data model was something very typical and that I could get the net profit without bidirectional filtering, because I can't lose the relationship between Calendar and Rappel. I didnt study computer science, I am an accountant who is loving this self-service BI thing so I don't know much (yet) about Data models, relationships etc... just learning for now. Could you imagine another way to get this net profit I am trying to achieve? I was trying for an hour with CALCULATE and VALUES por clientkey and month as filter parameters of CALCULATE, but didn't get what I wanted.

Here is a pic of the full data model in case you need something else to see:

Thanks for everything Dale, you are always here trying to help people!! 🙂

[v-jiascu-msft]

Hi @ContabilidadBI,

My pleasure. The formula couldn't be the problem. It's the context. If you use the proper fields of the proper table, the result would be good. The field "CodCliente" should be from table "Clientes" and the field "Date" should be from table "Calendario". I have updated it in the file: https://1drv.ms/u/s!ArTqPk2pu-BkgQTJqh7ZPl9CwAef

There are two potential loops in your model. That's why the "Cross filter direction" can't be "both". Refer to: powerbi-desktop-create-and-manage-relationships.

Best Regards!

Dale

[ContabilidadBI]

Hi @v-jiascu-msft,

Thank you so much for your help. I am going to study the issue about the relationships with the potential loops (thanks for the link) and will come back here next week. I didn't try the formula yet, because I could not set the 3 relationships as BOTH filter direction, so the problem must be in those potential loops you are describing.

Thanks, I am learning so much here!

[v-jiascu-msft]

Hi @ContabilidadBI,

According to my last test, you don't need to change the "Cross filter direction" settings. Let's wait and see the result of your test next week.

Best Regards!

Dale

[ContabilidadBI]

Hi @v-jiascu-msft,

I have tried your formula:

RentabilidadNeta = 
SUMX (
    FactLineas;
    FactLineas[Cantidad]
        * (
            FactLineas[CosteMinorado]
                - LOOKUPVALUE ( Facturas[Dto]; Facturas[CodFactura]; 'FactLineas'[CodFactura] )
        )
)
    - SUM ( Rappels[Rappel] )

But the results are not what I want, probably because I am doing something wrong or I didn't explain myself correctly (English is not my first language).

If I filter month for only January (Enero) and clients for only the clients who have the Rappel discount this is what I get:

I will put now the tables with only the rows needed to calculate this profit margin for only 1 client (Cod 205) and only for January, and calculate the result manually:

Header:

SerieFac

NumFac

Fecha

Estado

CodCliente

Total

Observaciones

BaseExenta

Neto

Dto

Ingresos

IVA

RE

CodFactura

HOSTELERIA

170010

31/01/2017

Cobrada

205

12.500,21

0, €

11.825,17

461,34

11.363,83

1.136,38

0

1-170010

Detail:

Cantidad	Precio	Ingresos	PrecioCoste	CodFactura	CosteMinorado
12,39	9,20	113,94	7,87	1-170010	7,38
6,17	9,20	56,72	7,87	1-170010	7,38
8,67	9,20	79,72	7,87	1-170010	7,38
13,47	9,20	123,92	7,87	1-170010	7,38
8,79	9,20	80,87	7,87	1-170010	7,38
5,83	9,20	53,59	7,87	1-170010	7,38
20,74	9,20	190,81	7,87	1-170010	7,38
5,67	9,20	52,16	7,87	1-170010	7,38
13,57	9,20	124,80	7,87	1-170010	7,38
5,88	9,20	54,05	7,87	1-170010	7,38
3,39	9,20	31,14	7,87	1-170010	7,38
14,28	9,20	131,33	7,87	1-170010	7,38
3,64	9,20	33,44	7,87	1-170010	7,38
6,66	9,20	61,23	7,87	1-170010	7,38
7,34	9,20	67,53	7,87	1-170010	7,38
6,99	9,20	64,31	7,87	1-170010	7,38
9,02	9,20	82,94	7,87	1-170010	7,38
8,51	9,20	78,29	7,87	1-170010	7,38
2,52	9,20	23,14	7,87	1-170010	7,38
10,21	9,20	93,93	7,87	1-170010	7,38
13,90	9,20	127,88	7,87	1-170010	7,38
4,32	9,20	39,74	7,87	1-170010	7,38
4,56	9,20	41,91	7,87	1-170010	7,38
12,62	9,20	116,10	7,87	1-170010	7,38
7,25	9,20	66,70	7,87	1-170010	7,38
7,75	9,20	71,30	7,87	1-170010	7,38
4,98	9,20	45,82	7,87	1-170010	7,38
10,57	9,20	97,20	7,87	1-170010	7,38
7,79	9,20	71,62	7,87	1-170010	7,38
4,21	9,20	38,73	7,87	1-170010	7,38
4,29	9,20	39,42	7,87	1-170010	7,38
8,78	9,20	80,73	7,87	1-170010	7,38
6,50	9,20	59,75	7,87	1-170010	7,38
9,24	9,20	84,96	7,87	1-170010	7,38
4,29	9,20	39,47	7,87	1-170010	7,38
113,04	6,30	712,12	4,99	1-170010	4,5
20,53	6,30	129,34	4,99	1-170010	4,5
81,59	6,30	514,02	4,99	1-170010	4,5
71,31	6,30	449,22	4,99	1-170010	4,5
41,20	6,30	259,53	4,99	1-170010	4,5
31,11	6,30	195,99	4,99	1-170010	4,5
82,42	6,30	519,21	4,99	1-170010	4,5
51,65	6,30	325,40	4,99	1-170010	4,5
205,22	4,89	1003,53	3,73	1-170010	3,24
102,13	4,89	499,39	3,73	1-170010	3,24
165,04	4,89	807,02	3,73	1-170010	3,24
102,88	4,89	503,06	3,73	1-170010	3,24
186,60	4,89	912,47	3,73	1-170010	3,24
134,77	4,89	659,00	3,73	1-170010	3,24
196,71	4,89	961,91	3,73	1-170010	3,24
154,35	4,89	754,77	3,73	1-170010	3,24

Rappel:

Fecha	CodCliente	Rappel
31/01/2017	205	566,929

So the formula manually is: Detail[Ingresos] - ( Detail[CosteMinorado] * Detail[Cantidad] ) - Header[Dto] - Rappels[Rappel] == 11825,17 - 8361,27 - 461,34 - 566.93 = 2435,63 € this should be the net profit margin for client 205 and month January I would like to achieve with DAX.

I would like to note that Detail[Ingresos] - Header[Dto] = Header[Ingresos]. I say this because maybe it is easier to use the Ingresos in the Header without having to substract the Dto (because it's already substracted). Also Detail[Ingresos] = Header[Neto] = 11825,17

I hope I explained myself better. Thanks for everything Dale, you are a star!

[v-jiascu-msft · Accepted solution]

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

[ContabilidadBI]

Hi @v-jiascu-msft,

It works like a charm, thank you very much. It was very easy formula at the end! I thought it couldn't be so simple. I always forget that the current context plays a huge role in DAX formulas.

Best Regards!
Víctor