Forum Discussion

ContabilidadBI's avatar
ContabilidadBI
Helper 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
Helper 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
Microsoft 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

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    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
    Helper III
    9 years ago

    Hi v-jiascu-msft,

     

    It works like a charm, thank you very much. It was very easy formula at the end! I thought it couldn't be so simple. I always forget that the current context plays a huge role in DAX formulas.

     

    Best Regards!
    Víctor

  • ContabilidadBI's avatar
    ContabilidadBI
    Helper 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!! :)

     

     

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

    Hi ContabilidadBI,

     

    My pleasure. The formula couldn't be the problem. It's the context. If you use the proper fields of the proper table, the result would be good. The field "CodCliente" should be from table "Clientes" and the field "Date" should be from table "Calendario". I have updated it in the file: https://1drv.ms/u/s!ArTqPk2pu-BkgQTJqh7ZPl9CwAef

     

    There are two potential loops in your model. That's why the "Cross filter direction" can't be "both". Refer to: powerbi-desktop-create-and-manage-relationships.

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     


     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

    Best Regards!

    Dale

     

  • ContabilidadBI's avatar
    ContabilidadBI
    Helper III
    9 years ago

    Hi v-jiascu-msft,

     

    Thank you so much for your help. I am going to study the issue about the relationships with the potential loops (thanks for the link) and will come back here next week. I didn't try the formula yet, because I could not set the 3 relationships as BOTH filter direction, so the problem must be in those potential loops you are describing.

     

    Thanks, I am learning so much here!

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

    Hi ContabilidadBI,

     

    According to my last test, you don't need to change the "Cross filter direction" settings. Let's wait and see the result of your test next week.

     

    Best Regards!

    Dale

  • ContabilidadBI's avatar
    ContabilidadBI
    Helper III
    9 years ago

    Hi v-jiascu-msft,

     

    I have tried your formula:

     

    RentabilidadNeta = 
SUMX (
    FactLineas;
    FactLineas[Cantidad]
        * (
            FactLineas[CosteMinorado]
                - LOOKUPVALUE ( Facturas[Dto]; Facturas[CodFactura]; 'FactLineas'[CodFactura] )
        )
)
    - SUM ( Rappels[Rappel] )

    But the results are not what I want, probably because I am doing something wrong or I didn't explain myself correctly (English is not my first language).

     

    If I filter month for only January (Enero) and clients for only the clients who have the Rappel discount this is what I get:

     


    I will put now the tables with only the rows needed to calculate this profit margin for only 1 client (Cod 205) and only for January, and calculate the result manually:

     

    Header:

     

    SerieFacNumFacFechaEstadoCodClienteTotalObservacionesBaseExenta Neto  Dto  Ingresos  IVA RECodFactura
    HOSTELERIA17001031/01/2017Cobrada20512.500,21 0, €    11.825,17           461,34      11.363,83       1.136,38  01-170010

     

    Detail:

     

    CantidadPrecioIngresosPrecioCosteCodFacturaCosteMinorado
    12,399,20113,947,871-1700107,38
    6,179,2056,727,871-1700107,38
    8,679,2079,727,871-1700107,38
    13,479,20123,927,871-1700107,38
    8,799,2080,877,871-1700107,38
    5,839,2053,597,871-1700107,38
    20,749,20190,817,871-1700107,38
    5,679,2052,167,871-1700107,38
    13,579,20124,807,871-1700107,38
    5,889,2054,057,871-1700107,38
    3,399,2031,147,871-1700107,38
    14,289,20131,337,871-1700107,38
    3,649,2033,447,871-1700107,38
    6,669,2061,237,871-1700107,38
    7,349,2067,537,871-1700107,38
    6,999,2064,317,871-1700107,38
    9,029,2082,947,871-1700107,38
    8,519,2078,297,871-1700107,38
    2,529,2023,147,871-1700107,38
    10,219,2093,937,871-1700107,38
    13,909,20127,887,871-1700107,38
    4,329,2039,747,871-1700107,38
    4,569,2041,917,871-1700107,38
    12,629,20116,107,871-1700107,38
    7,259,2066,707,871-1700107,38
    7,759,2071,307,871-1700107,38
    4,989,2045,827,871-1700107,38
    10,579,2097,207,871-1700107,38
    7,799,2071,627,871-1700107,38
    4,219,2038,737,871-1700107,38
    4,299,2039,427,871-1700107,38
    8,789,2080,737,871-1700107,38
    6,509,2059,757,871-1700107,38
    9,249,2084,967,871-1700107,38
    4,299,2039,477,871-1700107,38
    113,046,30712,124,991-1700104,5
    20,536,30129,344,991-1700104,5
    81,596,30514,024,991-1700104,5
    71,316,30449,224,991-1700104,5
    41,206,30259,534,991-1700104,5
    31,116,30195,994,991-1700104,5
    82,426,30519,214,991-1700104,5
    51,656,30325,404,991-1700104,5
    205,224,891003,533,731-1700103,24
    102,134,89499,393,731-1700103,24
    165,044,89807,023,731-1700103,24
    102,884,89503,063,731-1700103,24
    186,604,89912,473,731-1700103,24
    134,774,89659,003,731-1700103,24
    196,714,89961,913,731-1700103,24
    154,354,89754,773,731-1700103,24

     

    Rappel:

     

    FechaCodClienteRappel
    31/01/2017205566,929

     

    So the formula manually is: Detail[Ingresos] - ( Detail[CosteMinorado] * Detail[Cantidad] ) - Header[Dto] - Rappels[Rappel] == 11825,17 - 8361,27 - 461,34 - 566.93 = 2435,63 € this should be the net profit margin for client 205 and month January I would like to achieve with DAX.

    I would like to note that Detail[Ingresos] - Header[Dto] = Header[Ingresos]. I say this because maybe it is easier to use the Ingresos in the Header without having to substract the Dto (because it's already substracted). Also Detail[Ingresos] = Header[Neto] = 11825,17

     

    I hope I explained myself better. Thanks for everything Dale, you are a star!