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,

 

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