Skip to main content
cancel
Showing results for
Search instead for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper II

## 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

1 ACCEPTED SOLUTION
Super User

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 1
Super User

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.

## Helpful resources

Announcements

#### Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

#### Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors