View My Drafts
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 !
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.
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
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.
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 ?
Check the updated file and let me know if you are looking for something else.Regards,Rd
The result is perfect at product level, but it is not the one expected at aggregated level.
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.
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,