cancel
Showing results for
Did you mean:
Super Contributor

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

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

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

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

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!

Super Contributor

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

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

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

```RentabilidadNeta =
SUMX (
FactLineas;
* (
- 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:

 SerieFac NumFac Fecha Estado CodCliente Total Observaciones BaseExenta Neto Dto Ingresos IVA RE CodFactura HOSTELERIA 170010 31/01/2017 Cobrada 205 12.500,21 0, € 11.825,17 461,34 11.363,83 1.136,38 0 1-170010

Detail:

 Cantidad Precio Ingresos PrecioCoste CodFactura CosteMinorado 12,39 9,20 113,94 7,87 1-170010 7,38 6,17 9,20 56,72 7,87 1-170010 7,38 8,67 9,20 79,72 7,87 1-170010 7,38 13,47 9,20 123,92 7,87 1-170010 7,38 8,79 9,20 80,87 7,87 1-170010 7,38 5,83 9,20 53,59 7,87 1-170010 7,38 20,74 9,20 190,81 7,87 1-170010 7,38 5,67 9,20 52,16 7,87 1-170010 7,38 13,57 9,20 124,80 7,87 1-170010 7,38 5,88 9,20 54,05 7,87 1-170010 7,38 3,39 9,20 31,14 7,87 1-170010 7,38 14,28 9,20 131,33 7,87 1-170010 7,38 3,64 9,20 33,44 7,87 1-170010 7,38 6,66 9,20 61,23 7,87 1-170010 7,38 7,34 9,20 67,53 7,87 1-170010 7,38 6,99 9,20 64,31 7,87 1-170010 7,38 9,02 9,20 82,94 7,87 1-170010 7,38 8,51 9,20 78,29 7,87 1-170010 7,38 2,52 9,20 23,14 7,87 1-170010 7,38 10,21 9,20 93,93 7,87 1-170010 7,38 13,90 9,20 127,88 7,87 1-170010 7,38 4,32 9,20 39,74 7,87 1-170010 7,38 4,56 9,20 41,91 7,87 1-170010 7,38 12,62 9,20 116,10 7,87 1-170010 7,38 7,25 9,20 66,70 7,87 1-170010 7,38 7,75 9,20 71,30 7,87 1-170010 7,38 4,98 9,20 45,82 7,87 1-170010 7,38 10,57 9,20 97,20 7,87 1-170010 7,38 7,79 9,20 71,62 7,87 1-170010 7,38 4,21 9,20 38,73 7,87 1-170010 7,38 4,29 9,20 39,42 7,87 1-170010 7,38 8,78 9,20 80,73 7,87 1-170010 7,38 6,50 9,20 59,75 7,87 1-170010 7,38 9,24 9,20 84,96 7,87 1-170010 7,38 4,29 9,20 39,47 7,87 1-170010 7,38 113,04 6,30 712,12 4,99 1-170010 4,5 20,53 6,30 129,34 4,99 1-170010 4,5 81,59 6,30 514,02 4,99 1-170010 4,5 71,31 6,30 449,22 4,99 1-170010 4,5 41,20 6,30 259,53 4,99 1-170010 4,5 31,11 6,30 195,99 4,99 1-170010 4,5 82,42 6,30 519,21 4,99 1-170010 4,5 51,65 6,30 325,40 4,99 1-170010 4,5 205,22 4,89 1003,53 3,73 1-170010 3,24 102,13 4,89 499,39 3,73 1-170010 3,24 165,04 4,89 807,02 3,73 1-170010 3,24 102,88 4,89 503,06 3,73 1-170010 3,24 186,60 4,89 912,47 3,73 1-170010 3,24 134,77 4,89 659,00 3,73 1-170010 3,24 196,71 4,89 961,91 3,73 1-170010 3,24 154,35 4,89 754,77 3,73 1-170010 3,24

Rappel:

 Fecha CodCliente Rappel 31/01/2017 205 566,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!

Super Contributor

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

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

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

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

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

Announcements

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

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

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 6 members 1,071 guests
Recent signins: