Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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