I'm currently stuck with a problem, that puzzles me since a couple of weeks. I hope I can explain it in the correct terms...
Goal: I'd like to calculate the potential capacity of a production line for given down-time incidents.
A) "Incident table" with EventID, Start/End Timestamp, Classification, LineID, ...
B) "Line load table" with Timestamp, Width, Speed, LineID...
From A) I can easily calculate the total downtime either per incident or aggregated to category of incidents or per line.
From B) I can easily calculate average width and speed per production line and year
Issue: I'd like to calculate the potential capacity gain (if no incident occured). The math is simple: Width * Duration * Speed. BUT: It should not apply the "current" speed matching the timestamp (because, during an incident the speed is usually 0). Instead it is an agreement with Management Accounting to use the average speed and average width of that very production line in the given year. Now the "final" but: For clarity reasons I'd like to have a table that gives the potential capacity only by incident category -> w/o line & year on the rows - so I am not able to use year & lineID as filter context in the table!
Question: If I put my measure for average speed in a matrix (a.k.a. Pivot table) by year and line I have all the right values there. But how to pick one of these values to employ it in a measure (and secondly: how to dynamically reference to the right one?)
I very much hope, that there's someone out there, who can understand my issue!
Thanks in advance to all datanauts...
Hi @Jan_Orlandini ,
Could you please offer a sample data and post your desired result if possible?