Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
I am attempting to calculate the % of the material cost of a part based on the overall material cost of all parts in a given location. I assumed it would be straightforward with <single part cost>/<overall cost> but I am having trouble getting the denominator using Calculate and All functions, can anyone tell me what I am doing wrong?
I have one table with sales orders like so:
Warehouse | PartId | Invoice | Date | RepId | CustomerId | Sale Amt | Qty | |||||||
1 | 359 | 112 | 1/6/2023 | 1 | 878 | $6,453.10 | 1 | |||||||
2 | 372 | 113 | 1/6/2023 | 2 | 878 | $19,327.44 | 1 |
I have several other dimension tables connected to this, but most importantly I have a Part table with associated costs that is linked in a Many to 1 relationship to the Sales Order table:
PartId | Unit Cost | |
359 | $5,150 | |
372 | $15,500 |
First I create a measure for the cost unit cost: UnitCost = sumx('Part Table','Part Table'[Unit Cost]).
I also create a Qty measure just for convencience: OrderQty = sumx('Sales Orders','Sales Orders'[Qty])
Then I calculate the material cost for the order by multiplying Qty * Cost but I need to do some extra DAX in order to ensure that it multiplies and then sums (it was giving me incorrect totals because it was summing and then multiplying when I did [UnitCost] * [OrderQty]).
The ExtCost measure:
Solved! Go to Solution.
Use ALLSELECTED('Sales Orders') or REMOVEFILTERS() on the warehouse column.
Use ALLSELECTED('Sales Orders') or REMOVEFILTERS() on the warehouse column.
User | Count |
---|---|
22 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
26 | |
13 | |
11 | |
9 | |
6 |