Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Weighted distribution column

Hi,   I need to calculate Sum of Sales for all Clients where a specific product was sold. EX.:   I have a matrix table, with the products as Rows and I need the Total sales, Sum of Sales for all ...
Need Help
Show and Tell
  • v-lili6-msft's avatar
    v-lili6-msft
    6 years ago

    hi  Anonymous 

    Just adjust the formula as below:

     

    All products QTY at Clients where P was sold 2 = 
var _allclients=VALUES('Sales Table'[Client ]) 
Return
CALCULATE(SUM('Sales Table'[Qty]),FILTER(ALLEXCEPT('Sales Table','Sales Table'[Date]),'Sales Table'[Client ] in _allclients))

     

     

     

    Regards,

    Lin

Anonymous's avatar
Anonymous
Not applicable
6 years ago

v-lili6-msft thanks for your suggestion. It seems to be close, but it doesn't work for my example. I have played around, trying to "adapt" the formula, but I'm missing something. I suppose it is related to the filters that I have (Date and Product)

 

I've attached xlsx and .pbix files here (https://gofile.io/d/qx4AlR). I have no option to attach the files to thread, sorry.

 

v-lili6-msft's avatar
v-lili6-msft
Community Support
6 years ago

hi  Anonymous 

Just adjust the formula as below:

 

All products QTY at Clients where P was sold 2 = 
var _allclients=VALUES('Sales Table'[Client ]) 
Return
CALCULATE(SUM('Sales Table'[Qty]),FILTER(ALLEXCEPT('Sales Table','Sales Table'[Date]),'Sales Table'[Client ] in _allclients))

 

 

 

Regards,

Lin