Forum Discussion

Menvf's avatar
Menvf
Frequent Visitor
3 years ago

Challenge - Iterating measure

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?

1 Reply

  • Menvf , try if this can work

    Total Cost =
    SUMX(
    Summarize('Invoice Line', 'Invoice Line'[Customer.CustomerCategoryName], 'mainReseptitSQL'[ItemCode], 'mainReseptitSQL'[ComponentItemCode]),
    [Total Labor Cost for Semi-Finished Products] * [CM Sold No of Bags]
    )