cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
v-jiascu-msft Super Contributor
Super Contributor

Re: Problem calculating PROFIT with header and detail Fact Tables.

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.Problem calculating PROFIT with header and detail Fact Tables3.jpg

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 


Problem calculating PROFIT with header and detail Fact Tables4.jpg

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Best Regards!

Dale

 

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Problem calculating PROFIT with header and detail Fact Tables.

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

Re: Problem calculating PROFIT with header and detail Fact Tables.

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Problem calculating PROFIT with header and detail Fact Tables.

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:

 

Captura.PNG
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 Super Contributor
Super Contributor

Re: Problem calculating PROFIT with header and detail Fact Tables.

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 )

 

Problem calculating PROFIT with header and detail Fact Tables5.jpg 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Best Regards!

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Problem calculating PROFIT with header and detail Fact Tables.

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

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 6 members 1,071 guests
Please welcome our newest community members: