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 |
