For me, the easiest way to handle this is to separate each product and sum the total through measure. See attached.

Original Message:

Sent: Nov 24, 2022 05:26 PM

From: Norbert Marchand

Subject: Extrapolated margin based on past 12-month volumes and last available margin in €/kg

Hi,

The result is perfect at product level, but it is not the one expected at aggregated level.

In fact, if we take the month of december 2021 for products A and B, for example :

- the expected total extrapolated margin in value for the month should be the sum of extrapolated margin for each product for this month (which is not the case)
- and the corresponding total unit margin for the month should be calculated as the division of total extrapolated margin in value for this month by the total past 12-month volumes for this month

All this being as it is calculated in the excel file.

I know it's a bit tricky. Do you think you have a solution for that ?

Thanks again for your precious help on this.

Regards,

Norbert.

------------------------------

Norbert Marchand

Original Message:

Sent: Nov 24, 2022 07:29 AM

From: Rd 3nidad

Subject: Extrapolated margin based on past 12-month volumes and last available margin in €/kg

Hi Norbert,

Check the updated file and let me know if you are looking for something else.

Regards,

Rd

------------------------------

Rd 3nidad

Senior Finance Analyst

Original Message:

Sent: Nov 23, 2022 07:19 AM

From: Norbert Marchand

Subject: Extrapolated margin based on past 12-month volumes and last available margin in €/kg

Hi,

Thank you very much for this reply and for the report you built.

I can understand your confusion, but I anyway confirm that what I a m willing to calculate (at product level and aggregated level) is the multiplication of the margin of the month by the volumes of the past 12 months.

The objective is for me to answer the following question that I am asked for : with the current level of unit margin (in €/kg), what would be the value of my margin (in €) if I make the same volumes as the one made during the past 12 months.

Your model so far gives a picture of the margin in value for any given month by mutiplying the unit margin by the volumes of this single month. Which is obviously different.

Any idea how to answer my need ?

Best regards.

------------------------------

Norbert Marchand

Original Message:

Sent: Nov 22, 2022 01:57 AM

From: Rd 3nidad

Subject: Extrapolated margin based on past 12-month volumes and last available margin in €/kg

Hi Norbert,

I'm quite confused about your expected results where you sum up the full year and multiply with the margin on selected period, Is that what you really wish to achieve? Anyway, attached is the initial file where the calculation is by row basis. Let us start from here and further explain your requirement to finally achieve what you really wish to have.

Rd

------------------------------

Rd 3nidad

Senior Finance Analyst

Original Message:

Sent: Nov 21, 2022 11:37 AM

From: Norbert Marchand

Subject: Extrapolated margin based on past 12-month volumes and last available margin in €/kg

Hello,

Thanks for your answer.

By reading it, I realize that my description was not clear enoug.

Please find attached an excel file containing : a simplified dataset (spreadsheets called margin and volume) and a spreadsheet presenting the exepted results.

And hereunder what I did in PowerBI (which does not work at all).

To summarize, the objective is to calculate for each month and each product (and also at aggregated level) the extrpolated margin corresponding to the past 12 month volumes multiplied by the margin of the month for the selected product.

I guess it is very easy to do, but I can't find the right way to do it, feeling here a bit stupid...

Table 'Margin' :

Table 'volume' :

Data model :

Measures :

Result I get

------------------------------

Norbert Marchand

Original Message:

Sent: Nov 21, 2022 01:12 AM

From: Rd 3nidad

Subject: Extrapolated margin based on past 12-month volumes and last available margin in €/kg

Hi Norbert,

Why don't you create another 2 tables, 1st is the date table and the other one is product table? For the product table, just take it from the 2 tables you have, combine it with distinct values then connect your 2 tables to product and to date. Once link, then create again your measure.

You may share your sample file for much faster result.

Regards,

Ardie

------------------------------

Rd 3nidad

Senior Finance Analyst

Original Message:

Sent: Nov 19, 2022 08:29 AM

From: Norbert Marchand

Subject: Extrapolated margin based on past 12-month volumes and last available margin in €/kg

Dear all,

I need your help on the following.

I have a first table called "KPI Groupe Financials" with 3 columns : product, date, product margin in €/kg for the given date

I have a second table "KPI Groupe Volumes All" with 3 columns : product, date, product volumes in kg for the given date

There is one to multiple relationship between table 1 and table 2, based on a product and date concatenation.

My objective is to calculate, for each date and each product, the extrapolated margin measured as the product margin in €/kg for this product and date multiplied by the volumes of the past 12 months.

And I want this calculation to be still valid if I look at a group of products. Total extrapolated margin for this group of product must be relevant.

I have no problem with calculating the volumes for the past 12 months for each product and date. I use a sumx function in the second table to do so. Let's call 'VOLUME 12M' the output.

But I do not manage to calculate the extrapolated margin for each product and date.

I tried again to use a sumx function in the first table where I multiply the product margin in the first table by the VOLUME 12M.

Hereunder the measure I created :

It works at each individual product level. But it does not work when I look at the group of products.

In this case, the extrapolated margin is wrong.

The result I have is the sum of the individual product margins multiplied by the sum of the VOLUME 12M, which is not what I expect.

The result should be the sum of the individual extrapolated margin. Which is different.

I understand that my problem has to do with the context applied to the calculation. But I could not find the solution.

Hope this is clear enough

Anybody to help me ?

Thanks in advance !

------------------------------

Norbert Marchand

------------------------------