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