Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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.
Solved! Go to Solution.
Hi @Anonymous,
Please see the attached PBIX file update with your requirement. I have created a calculated column to calculate your desired result.
If this helped you, please mark this post as an accepted solution and like to give KUDOS .
Regards,
Affan
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/_total
You 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.
Hi @Anonymous,
Please see the attached PBIX file update with your requirement. I have created a calculated column to calculate your desired result.
If this helped you, please mark this post as an accepted solution and like to give KUDOS .
Regards,
Affan
@Anonymous
I have created a custom column
CustSum = CALCULATE(SUM(Table1[Sales]),FILTER(ALL(Table1),Table1[Client]=EARLIER(Table1[Client])))
Then I have created the following measure
WeightedAvg = var _total=CALCULATE(SUM(Table1[Sales]),ALL(Table1)) var _prodsum=CALCULATE(SUM(Table1[CustSum])) Return _prodsum/_total
Regards
Affan
Hi @affan
Many thanks for this!
it works fine if I only look at one day but I have years with data and I want to to show the % if I change from one day to 3 years (or what ever date/week/month/year)?
Many thanks,
Andreas
Hello @Anonymous
You just need to apply the filter within CALCULATE
CALCULATE( <your sum measure>, Products[Product] = 'Product1' )
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
90 | |
84 | |
70 | |
49 |
User | Count |
---|---|
143 | |
121 | |
112 | |
59 | |
58 |