Forum Discussion
Multiply subtotals between 2 matrix depending on different categories
Good morning everyone!
I am a new user of Power BI and I need some help to keep going.
I would like to multiply quantities of one matrix by the percentages calculated on another Matrix.
My goal is to forecast for next year what is going to happen on my sales per month, based on the % that my sales represented per month in the past years. If in average the month of January for the past 5 years represents 20% of my sales, I would like to multiply this % by what I expect to sell yearly in the upcoming year.
Every row is going to get multiplied by the total % of every month and not row by row ( normal).
Example: In September the countries are multiplied by 7,17 % ( The average of the 2 countries), and I would like the Country A to be multiplied by his related %: 15,88% and country B by 0,42%. Same for the productss
I would like to know how to multiply the % of sales related to the Country and Products.
I am unable to upload a pbix, here a link to the pbix on my google drive:
https://drive.google.com/file/d/1N1rIa68s1gjcGzlEkaqo_4wLE5k4r7r1/view?usp=sharing
I hope I explained correctly and someone can help!
Thank you so much!
Hi, OscarDOSSANTOS
Because you did not establish a relationship between the two tables, So there is a problem.
You need to create two table and use the field in your table visual instead of original table field(country and product), then you can get the correct value.
Like this:
Table = DISTINCT(Forecast[Country])Table 2 = DISTINCT(Forecast[Product])Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.
Best Regards,Community Support Team _ Janey
5 Replies
- lbendlin
Super User
Rather difficult to advise on this without seeing your data model. Would you be able to post a sample pbix file, or at least the data components?
As I don't have the option to upload the file, I share a link to download it from my google drive
https://drive.google.com/file/d/1N1rIa68s1gjcGzlEkaqo_4wLE5k4r7r1/view?usp=sharing
Thank you so much!
- v-janeyg-msft
Community Support
Hi, OscarDOSSANTOS
Because you did not establish a relationship between the two tables, So there is a problem.
You need to create two table and use the field in your table visual instead of original table field(country and product), then you can get the correct value.
Like this:
Table = DISTINCT(Forecast[Country])Table 2 = DISTINCT(Forecast[Product])Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.
Best Regards,Community Support Team _ Janey
Thank you so much! It works perfectly! I just find another difficulty I hope you can help me.
For every product there is specifications 1 and 2 that we can see from the past data. But the forecast doesn't include the specifications and I would like also to forecast the specifications based on Past data. To better understand:
Thank you so much! I hope I am clear enough, if not feel free to contact me.
Here the link with the new Power bi:
https://drive.google.com/file/d/1N1rIa68s1gjcGzlEkaqo_4wLE5k4r7r1/view?usp=sharing
Have a great week-end!
- v-janeyg-msft
Hi, OscarDOSSANTOS
Sorry, I tried to understand but there were difficulties. I am confused about your description, but it seems to be another big problem. I suggest you open a new post and explain it in detail so that we can help you soon.
Best Regards,
Community Support Team _ Janey
