Forum Discussion

Kfausch's avatar
Kfausch
Helper II
4 years ago
Solved

Table Totals Incorrect

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

Need Help
  • johnt75's avatar
    johnt75
    4 years ago

    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.

1 Reply

  • johnt75's avatar
    johnt75
    Super User
    4 years ago

    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.