Forum Discussion
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 ...
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
v-lili6-msft Thanks for your reply.
Table 1:
| Client | Product | Qty |
| C1 | P3 | 0.1 |
| C2 | P1 | 0.3 |
| C3 | P2 | 0.89 |
| C4 | P3 | 0.94 |
| C1 | P1 | 0.4 |
| C2 | P2 | 0.1 |
| C3 | P1 | 0.6 |
| C4 | P3 | 0.7
|
Table 2:
| Product | Naming Convention |
| P1 | Product 1 |
| P2 | Product 2 |
| P3 | Product 3 |
The outputs, based on datasets are:
| Clients | P Qty | All products QTY at All Clients | All products QTY at Clients where P was sold | Weighted Distribution | |
| P1 | 3 | 1.3 | 4.03 | 2.39 | 59% |
| P2 | 2 | 0.99 | 4.03 | 1.89 | 47% |
| P3 | 2 | 1.74 | 4.03 | 2.14 | 53% |
Appreciate your help.
hi Anonymous
Just create a meausre as below:
All products QTY at Clients where P was sold =
var _allclients=VALUES('Table 1'[Client ]) return
CALCULATE(SUM('Table 1'[Qty]),FILTER(ALL('Table 1'), 'Table 1'[Client ] in _allclients))
All products QTY at All Clients = CALCULATE(SUM('Table 1'[Qty]),ALL('Table 2'))Weighted Distribution = DIVIDE([All products QTY at Clients where P was sold],[All products QTY at All Clients])Result:
and here is sample pbix file, please try it.
Regards,
Lin
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.
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