Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
 View Only
Expand all | Collapse all

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

  • 1.  Extrapolated margin based on past 12-month volumes and last available margin in €/kg

    Posted 13 days ago
    Edited by Norbert Marchand 13 days ago

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


  • 2.  RE: Extrapolated margin based on past 12-month volumes and last available margin in €/kg

    Posted 12 days ago

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



  • 3.  RE: Extrapolated margin based on past 12-month volumes and last available margin in €/kg

    Posted 11 days ago
      |   view attached

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

    Attachment(s)



  • 4.  RE: Extrapolated margin based on past 12-month volumes and last available margin in €/kg

    Posted 11 days ago
      |   view attached

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

    Attachment(s)



  • 5.  RE: Extrapolated margin based on past 12-month volumes and last available margin in €/kg

    Posted 11 days ago
    Hi Norbert,

    Your model is wrong. Fix it at the first.
    Turn blue into active.
    What relation is between Margin and Volumes?


    Regards,

    ------------------------------
    Adam Artur Boltryk
    Business Analyst
    ------------------------------



  • 6.  RE: Extrapolated margin based on past 12-month volumes and last available margin in €/kg

    Posted 11 days ago

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

    Attachment(s)

    pbix
    Extrapolated.pbix   74 KB 1 version


  • 7.  RE: Extrapolated margin based on past 12-month volumes and last available margin in €/kg

    Posted 10 days ago

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



  • 8.  RE: Extrapolated margin based on past 12-month volumes and last available margin in €/kg

    Posted 9 days ago
      |   view attached

    Hi Norbert,

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

    Regards,
    Rd



    ------------------------------
    Rd 3nidad
    Senior Finance Analyst
    ------------------------------

    Attachment(s)

    pbix
    Extrapolated_1.pbix   78 KB 1 version


  • 9.  RE: Extrapolated margin based on past 12-month volumes and last available margin in €/kg

    Posted 8 days ago

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



  • 10.  RE: Extrapolated margin based on past 12-month volumes and last available margin in €/kg

    Posted 8 days ago
      |   view attached
    Hi Norbert,

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

    Rd

    ------------------------------
    Rd 3nidad
    Senior Finance Analyst
    ------------------------------

    Attachment(s)

    pbix
    Extrapolated_2.pbix   79 KB 1 version


  • 11.  RE: Extrapolated margin based on past 12-month volumes and last available margin in €/kg

    Posted 7 days ago

    Thank you. 

    Indeed, it works.

    But now, my problem is I have to apply this to a database containing 8.000 products.... Which, in the way you suggest, is just impossible.

    Would you have a solution that would be possible to scale-up to a very large database ?

    Regards,

    Norbert.



    ------------------------------
    Norbert Marchand
    ------------------------------