Forum Discussion

ContabilidadBI's avatar
ContabilidadBI
Icon for Helper III rankHelper III
9 years ago
Solved

Problem calculating PROFIT with header and detail Fact Tables.

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

  • v-jiascu-msft's avatar
    v-jiascu-msft
    9 years ago

    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

15 Replies

    • ContabilidadBI's avatar
      ContabilidadBI
      Icon for Helper III rankHelper III

      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's avatar
        v-jiascu-msft
        Icon for Microsoft Employee rankMicrosoft Employee

        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