Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
OscarDOSSANTOS
Frequent Visitor

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.

 

OscarDOSSANTOS_0-1631865640300.png

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!

1 ACCEPTED SOLUTION
v-janeyg-msft
Community Support
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])

 

vjaneygmsft_0-1632221729862.png

vjaneygmsft_1-1632221837720.png

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

View solution in original post

5 REPLIES 5
v-janeyg-msft
Community Support
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])

 

vjaneygmsft_0-1632221729862.png

vjaneygmsft_1-1632221837720.png

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:

 

OscarDOSSANTOS_1-1632483499548.png

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!

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

lbendlin
Super User
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!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.