Forum Discussion
Trouble Calculating Subtotals using All functions
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:
So now I would like to calculate % cost for a given sales order at a location: %ofCost = Divide([ExtCost],[TotalLocationCost]) but I am failing to get the correct amount for TotalLocationCost. I assume it is some combination of Calculate and All but I tried
I still have a bit of trouble wrapping my head around the ALL functions, can anyone point me in the right direction?
Use ALLSELECTED('Sales Orders') or REMOVEFILTERS() on the warehouse column.
1 Reply
- lbendlinSuper User
Use ALLSELECTED('Sales Orders') or REMOVEFILTERS() on the warehouse column.