Table Totals Incorrect

[Kfausch]

Hello,

I am struggling with a tables totals. I am not sure the correct way to calculate this so both the line and total values are correct.

I have two tables; 'Item Ledger' which stores the cost and 'Capacity Ledger' which stores the waste detail.

I am trying to correctly determine the cost of scrap values when they are categorized as "Damages" or "Overages". To do that I am summing the cost of the entire amount [Cost Amount Actual] and dividing it by the total number of units to get a Unit Cost. From there I am just multiplying the two numbers together to get the total cost of waste/scrap.

Waste $ = 
    VAR scrap = CALCULATE(SUM('Capacity Ledger'[Scrap_Quantity]),'Capacity Ledger'[Scrap_Code] = "Damages" || 'Capacity Ledger'[Scrap_Code] = "Overages")
    VAR unitCost = DIVIDE(SUM('Item Ledger'[Cost Amount Actual]), CALCULATE(SUM('Item Ledger'[Quantity]),'Item Ledger'[Entry_Type] = "Output"))
    
    RETURN ROUND((scrap*unitCost),2)

My problem is that when it totals, it is using the average Unit Cost rather than summing each row indepentantly. The total for Waste $ should be 175.86, but I am actually getting 174.94.

I am thinking I need to use SUMX in some fasion, but I am not sure what the correct implementation of this would be. The few things I tried did not work as expected. Does anyone know what I could do differently?

Thanks for reading!

Kevin

[johnt75 · Accepted solution]

You could rename your current measure to something like Waste Line Item then create a measure like

Waste $ =
IF (
    ISINSCOPE ( 'Table'[No] ),
    [Waste Line Item],
    SUMX (
        ADDCOLUMNS (
            SUMMARIZE ( 'Table', 'Date'[Date], 'Table'[No] ),
            "@waste", [Waste Line Item]
        ),
        [@waste]
    )
)

where the columns in the SUMMARIZE are the same columns from the left hand side of your table visual.