Forum Discussion
Weighted Distribution calculation
Hello,
Could you please help me to calculate weighted distribution using DAX?
I have the following table:
| Client | Products | Sales |
| 100042 | product1 | 24 |
| 100070 | product2 | 20 |
| 100070 | product1 | 24 |
| 100075 | product2 | 20 |
| 100075 | product3 | 24 |
| 100075 | product4 | 24 |
| 100081 | product2 | 20 |
| 100081 | product3 | 24 |
I need to calculate weighted distribution using DAX formula for each product, example of calculation for product1 is below:
| Product1 | Value | Description |
| Total sales of clients where product1 was sold | 68 | |
| Total sales of all clients | 180 | |
| Weighted distribution | 37.8% | 68/180 |
The main issue with calculation of [Total sales of clients where product1 was sold]. How to reach it in DAX PBI?
Thanks in advance.
8 Replies
Hello Anonymous
You just need to apply the filter within CALCULATE
CALCULATE( <your sum measure>, Products[Product] = 'Product1' )
Hi Anonymous,
You can used the below dax measure.
WeightedAvg = var _total=CALCULATE(SUM(Table1[Sales]),ALL(Table1)) var _prodsum=CALCULATE(SUM(Table1[Sales])) Return _prodsum/_totalYou can find the pbix file here.
If this helped you, please mark this post as an accepted solution and like to give KUDOS .
Regards,
Affan
Hi affan,
Thanks for the reply!
Unfortunately, this is not what I am asking for.
In your pbi-file for product1 weighted distribution is 26.67% but it should be 37.78%, numerator should be 68 not 48 (24+24+20).
I need to sum total sales of clients where product1 was sold. It means that I sum not only product1 but produt2 for client 100070.
