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