I am working on a cost follow up of labor for a production company.
I am having a real challenge defining a measure so it works like I need it.
One of the reports I want to build should display the dimensions
- 'invoice Line'[Customer.CustomerCategoryName]
- 'mainReseptitSQL[ItemCode]
- 'mainReseptitSQL[ComponentItemCode]
I have many tables and relationships, but the ones relevant for our measure is
1. mainReseptitSQL
2. Production - Trays and bags semi finished appended
3. Production analysis
4. Invoice Line
The relationship between the tables is
1. ONE. mainReseptitSQL: item code & component code -> MANY. Production - Trays and bags semi finished appended: item code & component code
2. MANY. Production - Trays and bags semi finished appended: semi finished part no <-> MANY. Production analysis: Part No
3. This is a secondary relationship, not active MANY. Invoice Line: Item.ItemCode <-> MANY. mainReseptitSQL: ItemCode
The table mainReseptitSQL is where I have the receipe for each product.
The table Production – Trays and bags is where I have the semi-finished product on the same row as the finished product.
The table Production analysis has the timings and quantity for production of the semi-finished.
I have 3 measures but one of them is not showing the wanted result.
The one that is not working is the one called Total Cost of Production.
What I want to do is calculating the actual total labor cost.
On the lowest granularity I am seeing the result I need, when looking at the dimensions. However, on higher dimension it's accumulating both measures before multiplying leading to a much higher value than wanted. What I need it to do is the multiplication for each row before summing. Not summing then multiplying. At least that is how I want it to act, not necessarily like the solution I am suggesting with the iteration.
What I want it to do is havin multiplying the measure [CM Sold No of Bags] with the measure [ Total Labor Cost for Semi-Finished Products]
Here are my measures
Total Cost of Production =
[ Total Labor Cost for Semi-Finished Products] * [CM Sold No of Bags]
CC Total Labor Cost Semi-Finished =
VAR LaborCostPerComponent =
COALESCE(
SUMX(
RELATEDTABLE('Production analysis');
(('Production analysis'[Pöytäkirja - Minutes] / 60) *
'Production analysis'[Number of persons] *
'Production analysis'[Hourly wage]) / SUM('Production analysis'[KG Produced])
);
0
)
RETURN
LaborCostPerComponent
Total Labor Cost for Semi-Finished Products =
VAR SummaryTable =
ADDCOLUMNS(
SUMMARIZE(
mainReseptitSQL;
mainReseptitSQL[ItemCode];
mainReseptitSQL[Finished demand per bag];
mainReseptitSQL[ComponentItemCode]
);
"LaborCostPerComponent"; [CC Total Labor Cost Semi-Finished]
)
RETURN
SUMX(SummaryTable; [LaborCostPerComponent] * [Finished demand per bag])
How can I define the measure Total Cost of Production so it multiplies on every row instead of multiplying the sum of each row?
