Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
102 | |
84 | |
79 | |
70 |
User | Count |
---|---|
120 | |
110 | |
95 | |
82 | |
77 |