cancel
Showing results for
Did you mean:
Highlighted
Member

## 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 header one: this table is the invoice table and look like this:

And then the detail table, where every invoice is disaggerated in every product sold in that invoice, and look like this: (cantidad is number of units)

This 2 tables are linked with CodFactura (which is the number of invoice). And my problem is the following:

In the header table there is a discount (Dto) so if I want to calculate the net profit I need to subtract the Dto first.

If I want to just calculate the gross profit I would use this formulas:

profit € = SUMX(
FactLineas;

profit % = DIVIDE(
[profit €];
SUMX(
FactLineas;
0
)

But, how can I calculate the NET PROFIT, taking into account the Discount in the header table? I only need to calculate and show this profit monthly and by client. Not products or categories or weird periods or anything else. Just the monthly profit (in € and %) by client.

To make things more complicated, there is another table with a special discount only for a few customers that looks like this:

So, Rappel is another discount that I would need to subtract too, and in this table, only a few clients appear, always the same clients, every month (CodCliente is Client Key).

Thank you so much for your help!! and sorry if my english is not very good. I am loving this software!!

1 ACCEPTED SOLUTION

Accepted Solutions
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.
15 REPLIES 15
Member

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

BTW, the header table has more columns, including date of course.

Thanks

Member

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

Any ideas?

To make the question simpler: how can I calculate the net margin taking into account the discount (which is in another related table)??

Thanks

Super Contributor

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

The records in the header table look like unique. So you could try the function "lookupvalue". The formula could be:

```profit € =
SUMX (
FactLineas;
* (
FactLineas[PrecioCoste]
)
)```

How does the last discount table connect with the other tables? Maybe you could try "lookupvalue" too. If you still have any question, please post the relationships and related data in the text mode.

BTW, if you post your data in the text mode, I think many people would be glad to help.

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.

Can you please inform me how can I put the data in text form?

Yes, CodFactura in the header table, is the primary key in the relationship with the detail table, so the values are unique. The last discount table has 2 relationships, client key that goes to the CLIENTS table and date that goes to the DATE table.

Thank you so much!!

Super Contributor

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

What are the relationships among the tables "clients", "header" and "detail"? This would the key point how we can deal with the discounts. Could you please post snapshot of the relationship?

There are two ways to post data in the text mode.

1. Copy the data (in a table most time) to Notepad, then copy the data from Notepad and paste here;

2. Copy the data, open "insert code" and paste.

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.

Hi @v-jiascu-msft, thanks again for your help.

Here you can see the relationships between header (Facturas), detail (Factlineas) and Clients (Clientes):

Header and detail are related by CodFactura (being the primary key in the header), and clients is related to the header by CodCliente which is the client key. And here you can see the relationship between clients and the last discount table (Rappel):

I will put the tables in text form in the following post.

Member

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

Detail:

 SerieFac NumFac CodArticulo Cantidad Precio Ingresos PrecioCoste CodFactura CosteMinorado 1 170010 00012 12,39 9,2 € 113,94 € 7,87 € 1-170010 7,38 1 170010 00012 6,17 9,2 € 56,72 € 7,87 € 1-170010 7,38 1 170010 00012 8,67 9,2 € 79,72 € 7,87 € 1-170010 7,38 1 170010 00012 13,47 9,2 € 123,92 € 7,87 € 1-170010 7,38 1 170010 00012 8,79 9,2 € 80,87 € 7,87 € 1-170010 7,38 1 170010 00012 5,83 9,2 € 53,59 € 7,87 € 1-170010 7,38 1 170010 00012 20,74 9,2 € 190,81 € 7,87 € 1-170010 7,38 1 170010 00012 5,67 9,2 € 52,16 € 7,87 € 1-170010 7,38 1 170008 00012 3,93 9,2 € 36,16 € 7,87 € 1-170008 7,38 1 170008 00012 4,80 9,2 € 44,11 € 7,87 € 1-170008 7,38 1 170008 00012 11,96 9,2 € 110,03 € 7,87 € 1-170008 7,38 1 170008 00012 4,61 9,2 € 42,41 € 7,87 € 1-170008 7,38 1 170008 00012 3,83 9,2 € 35,24 € 7,87 € 1-170008 7,38 1 170008 00012 5,59 9,2 € 51,38 € 7,87 € 1-170008 7,38 1 170012 00012 10,12 9,2 € 93,10 € 7,87 € 1-170012 7,38 1 170012 00012 9,90 9,2 € 91,03 € 7,87 € 1-170012 7,38 1 170012 00012 5,88 9,2 € 54,10 € 7,87 € 1-170012 7,38 1 170012 00012 5,10 9,2 € 46,92 € 7,87 € 1-170012 7,38 1 170012 00012 12,97 9,2 € 119,28 € 7,87 € 1-170012 7,38

Last Discount (Rappel):

 Fecha CodCliente Rappel 31/01/2017 8 513,962 31/01/2017 205 566,929 31/01/2017 247 396,897 31/01/2017 330 341,884 31/01/2017 367 125,912 31/01/2017 1000 125,539 28/02/2017 8 481,779 28/02/2017 205 613,189 28/02/2017 247 453,64 28/02/2017 330 532,904 28/02/2017 367 288,054 28/02/2017 1000 150,237 31/03/2017 8 485,963 31/03/2017 205 647,042 31/03/2017 247 475,174 31/03/2017 330 562,678 31/03/2017 367 237,343 31/03/2017 1000 219,996

Thanks again Dale, these aren't the full tables because they are too long but I guess this is enough.

Super Contributor

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

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[PrecioCoste]
)
)
- SUM ( 'Rappel'[Rappel] )```

I attached the PBIX here: https://1drv.ms/u/s!ArTqPk2pu-BkgQM-JwmUCi2S-9zf

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.

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

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: 294 members 3,056 guests
Recent signins: