using the sum of a column in all records as

[lukachko]

Good afternoon,

Im struggling to calculate and hold values in a table to calculate a percentage of downtime.

We have a table that contains records of a manufacturing machine state. one thing we are measuring is downtime. We have records of events of when a machine is down, why its down and for how long the machine is down for. This part of the calculation is pretty straight forward in a measure. The issue I have is trying to determine what the total availible time is.

I need to take the Sum total of the value (unplanned) for all records By "Date"(datetime) and by "shift" (numeric) . I am struggling to do this, I have tried to use combinations of Calculate,SUM and SUMX. I'm kind of stuck now.

The example is the following columns in the table. Availible time is how many minutes is availible by that work centre uninterrupted.
ALL_Unplanned downtime is the individual records unplanned downtime.
Actual time is what Im trying to calculate, the value I'm getting, vs the Value on the right (MANUAL CALC real actual)

I have solved this issue in the past by transposing the table but its definately not the right way to do it.
I also hope I don't need to build summary tables because we want to be able to filter and drill data in the BI report. 

WKC	Desc	Shift	Downtime	DowntimeDesc	ElapsedMinutes	AvailibleTime	All_Unplanned	actualTime	MANUAL CALC real actual
3764	3764 - T1XX LH	3	62	Welder	14.75	480	15	465.25	309
3764	3764 - T1XX LH	3	76	Light-Up	7.65	480	8	472.35	309
3764	3764 - T1XX LH	3	82	BREAK	38.18333333333334	480		480	309
3764	3764 - T1XX LH	3	84	Vision Issue	6.083333333333333	480	6	473.9166666666667	309
3764	3764 - T1XX LH	3	86	Driver Issue	31.066666666666666	480	31	448.93333333333334	309
3764	3764 - T1XX LH	3	91	Robot	110.93333333333334	480	111	369.06666666666666	309
3764	3764 - T1XX LH	4	88	Technical Troubleshooting	466.93333333333334	480	467	13.066666666666663	14
3764	3764 - T1XX LH	4	113	Masters	9.016666666666667	480		480

[lukachko]

Thanks for the solution. 

I got a little bit more fancy using summerize() and a related table to get my data lined up better.

[v-heq-msft]

Hi @lukachko ,
Does their answer solve your problem, if yes, you can mark their answer as a solution so that users with similar problems can find the solution faster, if not, you can continue to ask your question below.

Best regards,
Albert He

[rohit1991]

Hi @lukachko ,

You need to calculate Actual Time as:

Available Time - Sum of Unplanned Downtime for each Date & Shift

DAX Measure for Actual Time

ActualTime =
VAR TotalUnplanned =
    CALCULATE(
        SUM('Table'[All_Unplanned]),
        ALLEXCEPT('Table', 'Table'[Shift], 'Table'[Date])
    )
RETURN
MAX('Table'[AvailibleTime]) - TotalUnplanned

Explanation:

1. SUM('Table'[All_Unplanned]) → Sums unplanned downtime.
2. ALLEXCEPT('Table', 'Table'[Shift], 'Table'[Date]) → Keeps only the current Shift & Date while summing.
3. MAX('Table'[AvailibleTime]) → Ensures available time remains constant.

"The goal is to turn data into information, and information into insight." – Carly Fiorina

Need Power BI help? Connect on LinkedIn.

[rajendraongole1 · Accepted solution]

Hi @lukachko  - I have created actual time values by using the below fields, please check and let know.

Please find the attached pbix file. 

I hope this works.