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