Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
Hi all,
Sorry if this has been answered elsewhere, but looking for some hopefully easy help. I am new to Power BI having extensive knowledge in QlikSense. I know the logic around how this should be done (AGGR in QS) but am not finding the right/best way in DAX to handle.
Basically I would like to have the weighted average cost by customer and by product. A sample table and expected results are below. I'd like to pull this value in a whisker table with just one dot per customer and product - doing a straight average of Cost is misleading.
One small caveat - in the source data, there are different amount of rows per "cost per unit" per Customer as this is actually distribution customers, meaning Customer A might have 10 end-customers who buy through them at $1.00, 5 customers who buy through them at $0.80, etc. So we have to be careful about summing up Unit Cost and likely should use average/distinct instead.
I'm sure this is a mix of sumx, calculate, evaluate, etc but proving fruitless so far. Below is a table that I hope comes through ok.
Thank you for your help!
Product Customer Cost per Unit Units PurchasedTotal Cost Weighted Average Cost
A | Adams | $ 1.00 | 50 | $ 50.00 | |
A | Adams | $ 0.80 | 100 | $ 80.00 | |
A | Adams | $ 1.24 | 10 | $ 12.40 | $ 0.90 |
A | Smith | $ 1.25 | 50 | $ 62.50 | $ 1.25 |
A | Russel | $ 0.90 | 25 | $ 22.50 | $ 0.90 |
B | Adams | $ 25.00 | 21 | $ 525.00 | |
B | Adams | $ 30.00 | 10 | $ 300.00 | $ 26.61 |
B | Smith | $ 25.00 | 100 | $ 2,500.00 | |
B | Smith | $ 15.00 | 75 | $ 1,125.00 | $ 20.71 |
Solved! Go to Solution.
User | Count |
---|---|
21 | |
19 | |
12 | |
10 | |
9 |
User | Count |
---|---|
30 | |
26 | |
15 | |
13 | |
10 |