Forum Discussion

Alessandra's avatar
Alessandra
Icon for Helper I rankHelper I
8 years ago
Solved

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(...
Need Help
  • v-ljerr-msft's avatar
    v-ljerr-msft
    8 years ago

    Hi Alessandra,

     

    Based on my test, the formula below should work in your scenario. :smileyhappy:

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

    Here is the sample pbix file for your reference.

     

    Regards

Alessandra's avatar
Alessandra
Icon for Helper I rankHelper I
8 years ago

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 :D

 

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

 

v-ljerr-msft's avatar
v-ljerr-msft
Icon for Microsoft Employee rankMicrosoft Employee
8 years ago

Hi Alessandra,

 

Based on my test, the formula below should work in your scenario. :smileyhappy:

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

Here is the sample pbix file for your reference.

 

Regards

  • Alessandra's avatar
    Alessandra
    Icon for Helper I rankHelper I
    8 years ago

    v-ljerr-msft

    Thank you so much ! It's just what I needed :D