cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted

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:

 

Header.PNG

 

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)

 

Detail.PNG

 

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;
                    ([VentasBrutas] - (FactLineas[Cantidad]*FactLineas[Coste])))

 

profit % = DIVIDE(
                [profit €];
                SUMX(
                    FactLineas;
                    FactLineas[Cantidad]*FactLineas[Coste]);
                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:

 

Discount.PNG

 

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
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.
15 REPLIES 15

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

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

 

Thanks

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

v-jiascu-msft Super Contributor
Super Contributor

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

Hi @ContabilidadBI,

 

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

profit € =
SUMX (
    FactLineas;
    FactLineas[Cantidad]
        * (
            FactLineas[PrecioCoste]
                - LOOKUPVALUE ( Header[Dto]; Header[CodFactura]; 'FactLineas'[CodFactura] )
        )
)

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.

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

Thanks for your answer Dale.

 

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

v-jiascu-msft Super Contributor
Super Contributor

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

Hi @ContabilidadBI,

 

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.Problem calculating PROFIT with header and detail Fact Tables.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, thanks again for your help.

 

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

 

Captura.PNG

 

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

 

Captura.PNG

 

 

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

 

 

 

 

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

Header:

 

SerieFacNumFacFechaEstadoCodClienteTotalNetoDtoIngresosIVARECodFactura
HOSTELERIA17000104/01/2017Cobrada30495,7087087,008,701-170001
HOSTELERIA17000213/01/2017Pendiente24069,5161061,008,5101-170002
HOSTELERIA17000318/01/2017Cobrada31719,1417,4017,401,7401-170003
HOSTELERIA17000419/01/2017Cobrada90018,5116,24016,242,2701-170004
HOSTELERIA17000525/01/2017Cobrada207355,64340,3317,02323,3132,3301-170005
HOSTELERIA17000631/01/2017Cobrada368450,00409,090409,0940,9101-170006
HOSTELERIA17000731/01/2017Cobrada4192,43175,110175,1117,3201-170007
HOSTELERIA17000831/01/2017Cobrada811.392,9210728,74371,5410.357,201035,7201-170008
HOSTELERIA17000931/01/2017Cobrada20154,28140,250140,2514,0301-170009
HOSTELERIA17001031/01/2017Cobrada20512.500,2111825,17461,3411.363,831136,3801-170010
HOSTELERIA17001131/01/2017Cobrada246476,31433,010433,0143,301-170011
HOSTELERIA17001231/01/2017Cobrada2478.880,018233,3160,568.072,74807,2701-170012
HOSTELERIA17001331/01/2017Cobrada294875,28796,380796,3878,901-170013
HOSTELERIA17001431/01/2017Cobrada318221,58201,440201,4420,1401-170014
HOSTELERIA17001531/01/2017Cobrada327924,81840,740840,7484,0701-170015
HOSTELERIA17001631/01/2017Cobrada328110,22100,20100,2010,0201-170016

 

Detail:

 

SerieFacNumFacCodArticuloCantidadPrecioIngresosPrecioCosteCodFacturaCosteMinorado
11700100001212,399,2 €113,94 €7,87 €1-1700107,38
1170010000126,179,2 €56,72 €7,87 €1-1700107,38
1170010000128,679,2 €79,72 €7,87 €1-1700107,38
11700100001213,479,2 €123,92 €7,87 €1-1700107,38
1170010000128,799,2 €80,87 €7,87 €1-1700107,38
1170010000125,839,2 €53,59 €7,87 €1-1700107,38
11700100001220,749,2 €190,81 €7,87 €1-1700107,38
1170010000125,679,2 €52,16 €7,87 €1-1700107,38
1170008000123,939,2 €36,16 €7,87 €1-1700087,38
1170008000124,809,2 €44,11 €7,87 €1-1700087,38
11700080001211,969,2 €110,03 €7,87 €1-1700087,38
1170008000124,619,2 €42,41 €7,87 €1-1700087,38
1170008000123,839,2 €35,24 €7,87 €1-1700087,38
1170008000125,599,2 €51,38 €7,87 €1-1700087,38
11700120001210,129,2 €93,10 €7,87 €1-1700127,38
1170012000129,909,2 €91,03 €7,87 €1-1700127,38
1170012000125,889,2 €54,10 €7,87 €1-1700127,38
1170012000125,109,2 €46,92 €7,87 €1-1700127,38
11700120001212,979,2 €119,28 €7,87 €1-1700127,38

 

Last Discount (Rappel):

 

FechaCodClienteRappel
31/01/20178513,962
31/01/2017205566,929
31/01/2017247396,897
31/01/2017330341,884
31/01/2017367125,912
31/01/20171000125,539
28/02/20178481,779
28/02/2017205613,189
28/02/2017247453,64
28/02/2017330532,904
28/02/2017367288,054
28/02/20171000150,237
31/03/20178485,963
31/03/2017205647,042
31/03/2017247475,174
31/03/2017330562,678
31/03/2017367237,343
31/03/20171000219,996

 

 

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

 

v-jiascu-msft Super Contributor
Super Contributor

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

Hi @ContabilidadBI,

 

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[Cantidad]
        * (
            FactLineas[PrecioCoste]
                - LOOKUPVALUE ( Header[Dto]; Header[CodFactura]; 'FactLineas'[CodFactura] )
        )
)
    - SUM ( 'Rappel'[Rappel] )

Problem calculating PROFIT with header and detail Fact Tables2.jpg

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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.

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

Hi @v-jiascu-msft,

 

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:

 

Captura.PNG

 

 

Thanks for everything Dale, you are always here trying to help people!! Smiley Happy

 

 

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.

Top Kudoed Authors
Users Online
Currently online: 294 members 3,056 guests
Please welcome our newest community members: