Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!!
Solved! Go to 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
BTW, the header table has more columns, including date of course.
Thanks
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
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
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!!
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
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.
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.
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
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!! 🙂
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
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!
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
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!
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
97 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |