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

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!

 

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