cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Alessandra Regular Visitor
Regular Visitor

Calculate weighted distribution in a matrix

Hi everyone,

 

I'm trying to calculate how many products are in each bussiness by brand and manufacturer, 

I already have the denominator of the weighted distribution, which is:

 

CALCULATE(SUM([VolSales]);ALLSELECTED(Table[Manufacturer]);ALLSELECTED(Table[Brand]))

 

The result of this measure gives me the total volume sales, but I want to calculate the volume sale of each brand by manufacturer.

Any ideas? 

 

Thanks.

1 ACCEPTED SOLUTION

Accepted Solutions
v-ljerr-msft Super Contributor
Super Contributor

Re: Calculate weighted distribution in a matrix

Hi @Alessandra,

 

Based on my test, the formula below should work in your scenario. Smiley Happy

Measure = 
var businessIDs = DISTINCT(Hoja1[BusinessID])
return
CALCULATE(SUM(Hoja1[SalesVolume]),FILTER(ALL(Hoja1),CONTAINS(businessIDs,[BusinessID],Hoja1[BusinessID])))

r2.PNG

Here is the sample pbix file for your reference.

 

Regards

6 REPLIES 6
Super User
Super User

Re: Calculate weighted distribution in a matrix

Can you supply some sample data, sample output and the entire measure formula? What is your numerator?


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Alessandra Regular Visitor
Regular Visitor

Re: Calculate weighted distribution in a matrix

Hi @Greg_Deckler,

 

 

This is what I have of the numerator, but I don't know how to calculate the sales volume by business ID for each manufacturer and brand.

 

Numerator= CALCULATE(SUMX(SUMMARIZE(Table;Table[FechaD]);CALCULATE(SUMX(SUMMARIZE(Table;Table[BusinessID]);Table[SalesVolume])))))

 

In this picture, the red numbers are the numerator ( what I want to calculate), and the total is the denominator that I already have.

Captura.JPGCaptura11.JPG

 

 

v-ljerr-msft Super Contributor
Super Contributor

Re: Calculate weighted distribution in a matrix

Hi @Alessandra,

 

Is all the data in the same table or in different tables?

 

Could you post your real table structures(including the relationships) with some sample/mock data which can help us reproduce the issue, so that we can better assist on it? It's even better to just share a sample pbix file which can reproduce the issue. You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploadingSmiley Happy

 

Regards

Alessandra Regular Visitor
Regular Visitor

Re: Calculate weighted distribution in a matrix

Hi @v-ljerr-msft,

 

Thanks for answering.

All the data is in the same table.

I'm attaching an Excel where there is some of the data that I have in power bi and in that file there is a sample of what I'm trying to do in power bi, which is the sales for each category and value where all the businesses are present. For example, for this category and this value I want to see all the business where there are sales.

There is also a sheet named SQL where there is a query for calculting the distribution.

 

PS: Take as example ONLY the table from the right, the table from the left is just to see the difference of the sales per category and sale of all businesses where the category is present (what is trying to do in power bi) . The data from the table from the right was calculated manually and the steps are at the bottom.

Thanks in advance Smiley Very Happy

 

https://drive.google.com/file/d/1PT4u3XxhD2TGbyDo87hP20MsRCQWdD0p/view?usp=sharing

 

v-ljerr-msft Super Contributor
Super Contributor

Re: Calculate weighted distribution in a matrix

Hi @Alessandra,

 

Based on my test, the formula below should work in your scenario. Smiley Happy

Measure = 
var businessIDs = DISTINCT(Hoja1[BusinessID])
return
CALCULATE(SUM(Hoja1[SalesVolume]),FILTER(ALL(Hoja1),CONTAINS(businessIDs,[BusinessID],Hoja1[BusinessID])))

r2.PNG

Here is the sample pbix file for your reference.

 

Regards

Alessandra Regular Visitor
Regular Visitor

Re: Calculate weighted distribution in a matrix

@v-ljerr-msft

Thank you so much ! It's just what I needed Smiley Very Happy

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 46 members 1,374 guests
Please welcome our newest community members: