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

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

 

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

Hi v-jiascu-msft, thanks again for your help.

 

Here you can see the relationships between header (Facturas), detail (Factlineas) and Clients (Clientes):

 

 

Header and detail are related by CodFactura (being the primary key in the header), and clients is related to the header by CodCliente which is the client key. And here you can see the relationship between clients and the last discount table (Rappel):

 

 

 

I will put the tables in text form in the following post.

 

 

 

 

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

    Header:

     

    SerieFacNumFacFechaEstadoCodClienteTotalNetoDtoIngresosIVARECodFactura
    HOSTELERIA17000104/01/2017Cobrada30495,7087087,008,701-170001
    HOSTELERIA17000213/01/2017Pendiente24069,5161061,008,5101-170002
    HOSTELERIA17000318/01/2017Cobrada31719,1417,4017,401,7401-170003
    HOSTELERIA17000419/01/2017Cobrada90018,5116,24016,242,2701-170004
    HOSTELERIA17000525/01/2017Cobrada207355,64340,3317,02323,3132,3301-170005
    HOSTELERIA17000631/01/2017Cobrada368450,00409,090409,0940,9101-170006
    HOSTELERIA17000731/01/2017Cobrada4192,43175,110175,1117,3201-170007
    HOSTELERIA17000831/01/2017Cobrada811.392,9210728,74371,5410.357,201035,7201-170008
    HOSTELERIA17000931/01/2017Cobrada20154,28140,250140,2514,0301-170009
    HOSTELERIA17001031/01/2017Cobrada20512.500,2111825,17461,3411.363,831136,3801-170010
    HOSTELERIA17001131/01/2017Cobrada246476,31433,010433,0143,301-170011
    HOSTELERIA17001231/01/2017Cobrada2478.880,018233,3160,568.072,74807,2701-170012
    HOSTELERIA17001331/01/2017Cobrada294875,28796,380796,3878,901-170013
    HOSTELERIA17001431/01/2017Cobrada318221,58201,440201,4420,1401-170014
    HOSTELERIA17001531/01/2017Cobrada327924,81840,740840,7484,0701-170015
    HOSTELERIA17001631/01/2017Cobrada328110,22100,20100,2010,0201-170016

     

    Detail:

     

    SerieFacNumFacCodArticuloCantidadPrecioIngresosPrecioCosteCodFacturaCosteMinorado
    11700100001212,399,2 €113,94 €7,87 €1-1700107,38
    1170010000126,179,2 €56,72 €7,87 €1-1700107,38
    1170010000128,679,2 €79,72 €7,87 €1-1700107,38
    11700100001213,479,2 €123,92 €7,87 €1-1700107,38
    1170010000128,799,2 €80,87 €7,87 €1-1700107,38
    1170010000125,839,2 €53,59 €7,87 €1-1700107,38
    11700100001220,749,2 €190,81 €7,87 €1-1700107,38
    1170010000125,679,2 €52,16 €7,87 €1-1700107,38
    1170008000123,939,2 €36,16 €7,87 €1-1700087,38
    1170008000124,809,2 €44,11 €7,87 €1-1700087,38
    11700080001211,969,2 €110,03 €7,87 €1-1700087,38
    1170008000124,619,2 €42,41 €7,87 €1-1700087,38
    1170008000123,839,2 €35,24 €7,87 €1-1700087,38
    1170008000125,599,2 €51,38 €7,87 €1-1700087,38
    11700120001210,129,2 €93,10 €7,87 €1-1700127,38
    1170012000129,909,2 €91,03 €7,87 €1-1700127,38
    1170012000125,889,2 €54,10 €7,87 €1-1700127,38
    1170012000125,109,2 €46,92 €7,87 €1-1700127,38
    11700120001212,979,2 €119,28 €7,87 €1-1700127,38

     

    Last Discount (Rappel):

     

    FechaCodClienteRappel
    31/01/20178513,962
    31/01/2017205566,929
    31/01/2017247396,897
    31/01/2017330341,884
    31/01/2017367125,912
    31/01/20171000125,539
    28/02/20178481,779
    28/02/2017205613,189
    28/02/2017247453,64
    28/02/2017330532,904
    28/02/2017367288,054
    28/02/20171000150,237
    31/03/20178485,963
    31/03/2017205647,042
    31/03/2017247475,174
    31/03/2017330562,678
    31/03/2017367237,343
    31/03/20171000219,996

     

     

    Thanks again Dale, these aren't the full tables because they are too long but I guess this is enough.

     

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

      Hi ContabilidadBI,

       

      According to my test, "lookupvalue" would work properly. The trick could be the relationships. Please change the "cross filter direction" into "both". 

       

      net profit € =
SUMX (
    FactLineas;
    FactLineas[Cantidad]
        * (
            FactLineas[PrecioCoste]
                - LOOKUPVALUE ( Header[Dto]; Header[CodFactura]; 'FactLineas'[CodFactura] )
        )
)
    - SUM ( 'Rappel'[Rappel] )

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

      I attached the PBIX here: https://1drv.ms/u/s!ArTqPk2pu-BkgQM-JwmUCi2S-9zf

       

      Best Regards!

      Dale

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

        Hi v-jiascu-msft,

         

        Sorry for the delay, I just got the time today to try and replicate what you did for me. Unfortunately, when I try to set thoes 3 relationships as bidirectional filtering I always get this error message in one of them:

        The relationship you are creating allows to filter Calendar by Clients, but Power BI Deskpot only allows 1 rute of access of filtering between the tables of a data model. Desactivate the relationships between the tables or change the filter direction. This relationship can be set as unidirectional filtering.

        I was thinking that this "header and detail Sales" data model was something very typical and that I could get the net profit without bidirectional filtering, because I can't lose the relationship between Calendar and Rappel. I didnt study computer science, I am an accountant who is loving this self-service BI thing so I don't know much (yet) about Data models, relationships etc... just learning for now. Could you imagine another way to get this net profit I am trying to achieve? I was trying for an hour with CALCULATE and VALUES por clientkey and month as filter parameters of CALCULATE, but didn't get what I wanted.

         

        Here is a pic of the full data model in case you need something else to see:

         

         

         

        Thanks for everything Dale, you are always here trying to help people!! :)