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...
Need Help
  • 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

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

BTW, the header table has more columns, including date of course.

 

Thanks

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

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
    9 years ago

    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

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

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

      • v-jiascu-msft's avatar
        v-jiascu-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        9 years ago

        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