I am having a problem with my total column and getting the sum to work correctly on of of the field. It's a measure which goes off of a couple other measures and i can't get the total to work.
The data is as follows:
| Item No. | CurrentCost | BaselineCost | InvoicedQty | UnitCost | BLUnitCost | UnitPPV | PPV |
| 102140 | 11,802 | 25,237 | 2,400 | 4.92 | 4.85 | .06 | 154.72 |
| 1201700 | 6,277 | 7,076 | 29 | 216.44 | 228.25 | -11.81 | -342.37 |
| TOTAL | 18,079 | 32,312 | 2,429 | 7.44 | 6.18 | 1.27 | 3,075 |
All of the cells in green above are working correct. The cells in purple don't work, but I don't need the totals on them. The Red one isn't working and I need it to work. The total i need it to show is the following: -187.65
Explanation of each of the columns:
1. Current Cost:
Summary: The Cost of the purchase of the item YTD.
Dax: CurrentCost =
var Year = [SelectedYear]
return
SUMX(
'Inventory Transactions_V',
CALCULATE(
CALCULATE(
Sumx('Inventory Transactions_V','Inventory Transactions_V'[Cost]),
Filter('Inventory Transactions_V', YEAR('Inventory Transactions_V'[Posting Date]) = Year),
FILTER('Inventory Transactions_V','Inventory Transactions_V'[Item Ledger Entry Type Description] = "Purchase")
)
)
)
2. Baseline Cost: The purchase cost of the item YTD. The dax is the same as Current Cost, except the year is -1
3. Invoice Qty: The purchase quantity of the item YTD. The dax is the same as Current Cost, except the summed field is qty
4. Unit Cost: the average unit cost for Current Year UnitCost = [CurrentCost]/[InvoicedQty]
5. BL Unit Cost: the average unit cost for Prior Year UnitCost = [CurrentCost]/[InvoicedQty]
6. Unit PPV: The difference between the average cost of an item this year vs last year. UnitPPV = [UnitCost]-[BLUnitCost]
7. PPV: The total cost difference between the items purchased this year, applied to those purchased this year. PPV = [UnitPPV]*[InvoicedQty]
I know its something to do with my SUMX and Calculate in the measures which lead into the PPV measure, but I haven't been able to figure it out. I would greatly appreciate any help.
