Forum Discussion

OscarDOSSANTOS's avatar
OscarDOSSANTOS
Frequent Visitor
4 years ago
Solved

Calculate different values in one column based on different % that results from another matrix

Good evening everyone,

Thank you for reading my post I hope you can help me for this really specific case!

 

I sell products in different countries with different specifications. Here I created a matrix where I can observe the amount of sales for the previous years per month and how much it represents in % of the total sales:

 

Then I created a matrix where there is the forecast by Country and Product for next year, as follows:

The forecast it goes as far as the product level. I cannot forecast down to the Specification level. So I want to use the % of specification sells of previous years to forecast by Country, by product and also by specification.

 

To do that I multiply this 2 matrix and end up with a forecast for next year by month:

 

We have good results for the Product and Country levels but not for the specifications. The formula used is the following.

Sales 2021 = [% of Sales]*[Total Sales Forecast]
 
The % of the of the sales by country is multiplied by the total forecast by country. The % of the sales by product is multiplied by the total forecast by product. The error comes where the specifications are not multiplied by the total of the product per month but multiplied by the total of the product per year. (normal as there is no forecast per specification)
 
Example: In the past, in November, for the Product 1, there was 9 "specification 1" and 89 "specification 2". Total of 98.
The weight in % of "specification 1" is 9,2% and for "Specification 2" is 90,8%.
 
I would like this % to be multiplied by the result of the forecast of Product 1. In this case for November 55.58.
And as you can see in the last matrix the specification 2 is already 76. 
 
I want the sum of specifications equal to the total of Product per month:
% of Specifiation 1 * TotalProduct1permonth + Specification 2 * TotalProduct1permonth = TotalProduct1permonth
 
As follows(example):
 
 November
 Sales 2021
Country A92.99
Product 155.58
Specification 1       50,47
Specification 25.11

 

It's a really confusing exercice, I tried to be as clear as possible. Please feel free to let me know if I need to be clearer.

 

A link on my drive to download the power bi:

 

https://drive.google.com/file/d/1vmOmsuNQD9ks5NYX2b8V-rFy2JjjfGS5/view?usp=sharing

 

Thank you so much for your time!

Oscar

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    HI OscarDOSSANTOS,

    According to your description, it sounds like you want to get the per month forecast instead of total values. If that is the case, I'd like to suggest you manually process these.
    You can get the current year forecast and manually extract aggregate date values on the current cell, then you can use the total month count to calculate with summarized total values. (per month forecast = year total /12, current forecast = per month forecast * current aggregated month amount)
    Regards,
    Xiaoxin Sheng

3 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    4 years ago

    You are calculating percentage of row total (across all months). Are you sure about that? Don't you want to calculate the percentage of parent total (single product and single month) ?

    • OscarDOSSANTOS's avatar
      OscarDOSSANTOS
      Frequent Visitor
      4 years ago

      I guess it can also work to calculate the percentage of parent total

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    HI OscarDOSSANTOS,

    According to your description, it sounds like you want to get the per month forecast instead of total values. If that is the case, I'd like to suggest you manually process these.
    You can get the current year forecast and manually extract aggregate date values on the current cell, then you can use the total month count to calculate with summarized total values. (per month forecast = year total /12, current forecast = per month forecast * current aggregated month amount)
    Regards,
    Xiaoxin Sheng