Power BI Builds 18 - OEE Manufacturing Report

Hi I am somewhat new to using Power BI. I have been on Enterprise DNA for about 6 month. I am accountant who doesn’t use Power BI for work but I want to develop this skill more so I am trying to do this challenge so that I can gain some practical experience.

I having been struggling with the numbers making sense but they may not make logical sense because from the above comments stating that the data it is random. This helps a little.

My main question is regarding using calculate. I have a measure that sums up all of duration hours but I can not seem how to ensure this duration hours always keeps the week ending into context no matter if I am looking at machine, products or another category. See below screen shot.
image

Any help would be appreciated.

Thanks.

David

1 Like

@DavidHalliday Hello and welcome! I ran into the same thing. Try using COUNT instead of SUM.

1 Like

Hi all,
Are you considering the accumulated units of biscuits made as units for all types of machines or do you have to take into account the type of machine to know if it works with units or boxes?
For the calculation of the biscuits made from the table of stops, I am subtracting the current accumulated minus the accumulated of the previous record for that machine and product, taking zero for the first record. I don’t know if there is a better way to calculate it.

Thanks.

2 Likes

Hi Jose,
As for machine units, Haroon posted the following in a previous post:

as long as you are consistent with units please feel free to interpret as you see best

You can see @paidcritic and I had two different interpretations. Either way the data randomization can potentially produce some illogical numbers and therefore Haroon’s advice that consistency is more important :slight_smile:

For biscuit calculations, my assumption from my previous post on this thread :

I interpreted the following from the brief to mean both total and good biscuits made are cumulative:
“For each stoppage the cumulative number of biscuits produced is also recorded along with the good biscuits allowing us to deduce waste.”

Since they are the same context then each stoppage row will give you a %waste without having to consider previous rows.

2 Likes

Hello darandavies,
As you say, to calculate the waste we can use the accumulated data of TotalBiscuitsMade and GoodBiscuitsMade but for the performance calculation:

Performance % = Filled Consumer Units/Design Performance*100

For Filled Consumer Units, if a machine has accumulated 500 cookies at 10:00 in the morning and 800 at 12:00, would the cookies made be 300 or 1,300?

Thanks a lot.

I approached this data challenge like I do with any other data project; dig into the raw data and make sense of it. I admit I know nothing of OEE but now I do. I found myself doing a lot of reading on OEE and the KPI measures that are needed to let the manufacturing unit know how to improve production with the machines and to see what issues arise.

So after looking into the data, I thought of a learning video where Sam McKay brought up the Data Model Planner. It helps to get organized in the beginning with not only your layout but your thoughts on how to tackle any data.

This challenge is unlike my last one as far as thought process. I started thinking of color schemes and layouts. I chose simple blues and a cleaner background; not loud and overwhelming.

But as I went through the data and starting building simple DAX measures then branching out, I noticed some numbers just weren’t making sense. I did my best to showcase the best visuals that would provide decent insights in the making of biscuits (cookies if you’re a Yankee, wink wink).

I started to realize that Availability and Efficiency are key components to OEE. Below are some definitions.

Availability

The goal should be at least 90%. Grandma’s Biscuits had an overall net % of 89.8 and a gross of 81.5%

Gross Availability should indicate when there is excess capacity in the equipment, in case it is needed.

Net Availability use the scheduled (excluding external causes) run time divided by the actual run time, realizing that the equipment has unused capacity. This allows personnel to concentrate on maximizing the equipment performance during the scheduled time, always realizing that there is additional capacity if it is ever needed.

Efficiency

The goal here should be at least 95%.

So back to the topic of some values just didn’t make sense. Some of the biscuits had an OEE Gross % of 0.0. Because it’s availability was 95.4%. Meaning they did not make those biscuits often. A great example of maybe digging into as of why values are low would be Jammy Creams. This particular cookie had a performance of 76.2% and had 912 minutes consisting of Changeover Cleaning or No Order status. OEE was 26%.

When looking at the Details page hover over the table and a tooltip will appear showing some KPIs.

In this challenge, I used again branching out DAX measures, separate measure folders to indicate if they were related to KPI, OEE, Time or Target.

I used Dynamic Measures for titles, TREATAS with Summarize measures for bringing in values from other tables that didn’t have a relationship, RANKX with ISINSCOPE ( this was new to me), I always started adding comments inside the DAX code to help not only me but others the logic.

Rank Product OEE Gross = – ISINSCOPE is useful to detect if a column is currently
– in the rows or columns of a visual (like groupby columns
– of SUMMARIZECOLUMNS) AND it has only one value visible
IF (
ISINSCOPE( ‘Fact’[Product]),
RANKX (
CALCULATETABLE (
VALUES ( ‘Fact’[Product] ),
ALLSELECTED ( ‘Fact’[Product] )
),
[OEE Gross %]
)
)

Total Count of Biscuits/Pallet = //Filled Consumer Units
CALCULATE([Total Good Bicuits],
TREATAS(SUMMARIZE(‘Fact’, ‘Fact’[Product]),
‘Product’[Total # Biscuits/Pallet]
)
)

Used this DAX specifically to single out the OEE category:

Total NO (min) = CALCULATE([Total Duration (min)],‘Fact’[OEE Category] IN { “NO (No Order)”})

Lastly, the value of zero in the OEE Category is probably an error on the machine end or the value entered when it is not know or not entered correctly.

Anyway, this is my brief summary of this challenge. I look forward to the next. Brian knows that I had accidently cut instead of copy my PBIX files to another drive location and overwrote the files. So I had to start over this week, then a power outage occurred during this week also; so I had to scramble to get this done FAST!! I hope I didn’t miss anything.

Paul

6 Likes

Excellent!!

2 Likes

Thank you.

@Paul.Gerber - absolutely outstanding! Just a terrific all-around job on a very challenging dataset. Really thorough analysis, great-looking theme and navigation, and some excellent visualization choices and tooltips (I particularly like the use of the Decomposition Tree and the KPI tooltip).

Also, thank you for taking the time to post the detailed report on your development process and how you dealt with some of the anomalies in this dataset. Much for everyone to learn from this one. Well done, sir! :clap: :clap:

3 Likes

My last challenge was challenge 10 ,YES ! I missed seven challenges, hope this new report cover my missed ones.
So here is my submission for Challenge 18 - OEE Manufacturing Report.

  • Some Dax Measures from the report :

    1. GoodBiscuitsMade Inc.(Calculate the incremental values for good quality biscuits)

GoodBiscuitsMade(Inc.) =
VAR Machines = ‘Fact’[MachineID]
VAR Products = ‘Fact’[ProductID]
VAR Datees = ‘Fact’[StartDate]
VAR Timee = ‘Fact’[StartTime]
VAR PrevRow =
CALCULATE (
MAX ( ‘Fact’[GoodMadeBiscuits] ),
FILTER (
‘Fact’,
‘Fact’[MachineID] = Machines
&& ‘Fact’[StartDate] = Datees
&& ‘Fact’[ProductID] = Products
&& ‘Fact’[StartTime] < Timee
)
)
RETURN
IF (
PrevRow = BLANK ()
|| NOT ( machines = 1 )
|| ‘Fact’[GoodMadeBiscuits] = BLANK ()
|| PrevRow > ‘Fact’[GoodMadeBiscuits],
RANDBETWEEN ( 800, 1800 ),
‘Fact’[GoodMadeBiscuits] - PrevRow
)

  1. Available Gross_in Mins(calculated the peroid selected for all the machines by date and time ) =
    VAR MinDateSelected =
    VALUE (
    COMBINEVALUES (
    " ",
    MIN ( Dates[Date] ),
    TIME ( MIN ( ‘Start Time Table’[Hour] ), 00, 00 )
    )
    )
    VAR MaxDateSelected =
    VALUE (
    COMBINEVALUES (
    " ",
    MAX ( Dates[Date] ),
    TIME ( MAX ( ‘End Time Table’[Hour] ), 59, 59)
    )
    )
    VAR Result =
    DIVIDE( DATEDIFF ( MinDateSelected, MaxDateSelected, SECOND ),60,0)
    RETURN
    Result*‘Availability Measures’[Machines Number]

  2. CC Changeover / Cleaning (calculated the downtime for “CC” category filtred by date and time ) =
    VAR MaxDate =
    MAX ( Dates[Date] )
    VAR MinDate =
    MIN ( Dates[Date] )
    VAR StartTime =
    TIME ( MIN ( ‘Start Time Table’[Hour] ), 00, 00 )
    VAR EndTime =
    TIME ( MAX ( ‘End Time Table’[Hour] ), 59, 59 )
    VAR StartDT = MinDate + StartTime
    VAR EndDT =
    COMBINEVALUES ( " ", MaxDate, EndTime )
    VAR Result =
    CALCULATE (
    SUM ( ‘Fact’[Duration] ),
    FILTER ( ‘Fact’, ‘Fact’[Duration] > 3 ),
    FILTER (
    ‘OEE Categories’,
    ‘OEE Categories’[OEE Category] = “CC (Changeover Cleaning)”
    ),
    FILTER (
    ‘Fact’,
    OR (
    AND ( ‘Fact’[StartDate] = MinDate, ‘Fact’[StartTime] >= StartTime ),
    ‘Fact’[StartDate] > MinDate
    )
    ),
    FILTER (
    ‘Fact’,
    OR (
    AND ( ‘Fact’[EndDate] = MaxDate, ‘Fact’[EndTime] <= EndTime ),
    ‘Fact’[EndDate] < MaxDate
    )
    )
    )
    RETURN
    Result

9 Likes

As always - some great reports here. I am bummed to have missed this one, but as always - look forward to seeing the reports and getting great ideas out of them.

3 Likes

Excellent report!!

1 Like

Here’s @hastewar22 entry for Power BI Challenge 18.

image

image

image

image

6 Likes

Here’s the entry from one of our non-member participants, AW.

2 Likes

Here’s @ibesmond entry for Power BI Challenge 18.

1 Like

Started my journey learning a bit more about OEE. I wanted to create visual showing the runtime and status (working, major, minor) – something I did not find in the visual library. Also wanted to split multi-day outages to create separate rows for each day (production reports would probably drilldown to shift.) Some problems include overlapping outages and start/end datetime in minutes but durations in seconds. (Will be interested to learn from other participants how they addressed these issues.) The snippet below from the Add Good tab of the pbix shows this.


After a bit of wandering around the power query landscape looking for signposts left by Melissa and BrianJ I was able to create the visual shown on the Initial tab from the Fact query.

And to get this annotated with OEE Cat and Duration and zoomable but not as a dynamic visual in Power BI – these two images are from R Studio.
image
image
This view is missing the good or Working time – which should also have the Total and Good biscuit production values allocated and added to the tooltip. To show working time I need to add working rows to the Fact table. Will move the total and good biscuits produced to these rows.
So, back to power query to create rows for working times. The query Fact2_good was the result and when merged back with Fact2_outage to get a table with rows for outages and for good production.
The Add Good tab visual now shows all three states for a particular selection of biscuit and machine.

The attached html is an active document (zoom and annotate from R Studio.) The figure shows one zoomed region.
test.html (5.5 MB)
image
However, this is not usable in the Power BI report. Off to look at Charticulator to see if it can be used to create this visualization with a play axis and zoom capability.
I did not finish this challenge – let myself get distracted chasing this visual. And I am confident my queries are rather ugly (and long), but I learned a lot getting here. Thanks for the challenge,
tim
StripChart.pbix (853.0 KB)

Hello Rachwen_Mesbehi,
I congratulate you for the fantastic work done on this dashboard, in terms of design and functionality. It is quite complete, not only does it have the factors on which the OEE depends, but you also analyzed the types of stops and the MT KPIs.
In my case, I am still struggling with the calculation of the formulas. After seeing the Dax calculations that you have made, I will tell you some questions that arise.

  1. In the calculation of the units produced, I have done it by subtracting the previous value from each record per machine and product, but for the first record I see that you use a random value between 800 and 1800. In my case, I did not know what to apply if zero or accumulated value. Having only the data of the stops and the accumulated ones, I think that it is not possible to calculate the real production.
  2. In the calculation of the stop times, I have done it at the day level but I have found the case that the stop time belongs to two days, for example if the machine is stopped for two hours from 11:00 p.m. until 1:00 a.m. the next day. Here we cannot add the duration but we have to calculate the part that corresponds to each day.
  3. The performance data is given to us by machine and product, so to calculate the performance at the speed of the contract, we should know the effective time that a machine is working on that product. We can calculate the downtime but I don’t know how to calculate the gross time available for each machine and product. I have considered that there is a machine for each product, with 24 gross hours, so for 07/01/2021 I get 7 machines with 168 gross hours.
    Thanks a lot.
1 Like
  • A1 - I noticed that we have to many zero in the cumulativebiscuits colomn also in some cases we have the previous row is bigger than the next one this why I used RANDBETWEEN FUNCTION to add more sense to the oee measures
    IF (
    PrevRow = BLANK ()
    || NOT ( machines = 1 )
    || ‘Fact’[GoodMadeBiscuits] = BLANK ()
    || PrevRow > ‘Fact’[GoodMadeBiscuits],
    RANDBETWEEN ( 800, 1800 ),
    ‘Fact’[GoodMadeBiscuits] - PrevRow
    )

  • A2-In some cases as you said, if the machine is off from 01-07-21 11 PM until 02-07-21 1 AM of the next day as per the downtime measure I created, the downtime will be added to the next day (two hours will be added to the total downtime of 02- 07-21)
    FILTER (
    ‘Fact’,
    OR (
    AND ( ‘Fact’[StartDate] = MinDate, ‘Fact’[StartTime] >= StartTime ),
    ‘Fact’[StartDate] > MinDate
    )
    ),
    FILTER (
    ‘Fact’,
    OR (
    AND ( ‘Fact’[EndDate] = MaxDate, ‘Fact’[EndTime] <= EndTime ),
    ‘Fact’[EndDate] < MaxDate
    )
    But it would be better if we divide the downtime into two days ( 1 hour for 01-07-21 and 1 hour for 02-07-21 )

  • A3 -The total time available for one machine is 24 hours in a day, so if we have five machines running on that day, we should have 24 * 5 = 120 total hours on that day.
    VAR Result =
    DIVIDE( DATEDIFF ( MinDateSelected, MaxDateSelected, SECOND ),60,0)
    RETURN
    Result‘Availability Measures’[Machines Number]*

Thanks @jafernandezpuga for your lovely feedback !

4 Likes

Hi everyone,

This is my entry for Power BI Challenge 18.

This current challenge introduced me to OEE. It was really fun trying to find my way out to get meaningful insight and then picking the suitable model, visual and theme for the challenge. However, here I am.

You can click here
or use this URL directly to view https://app.powerbi.com/view?r=eyJrIjoiYzFkOWJlNzgtZmMxOS00MDcxLWE4NDItMmMwMGMzMzgwYjY1IiwidCI6IjA2MzNmYTY1LTcwN2EtNDVmNy1hOTAwLTIxOTE2ZWMxNGRlOSJ9&pageName=ReportSectionc61443ac188526d463c0

The OEE Calculation
The preferred OEE calculation is based on the three OEE Factors: Availability, Performance, and Quality.

OEE Formula
OEE takes into account all losses, resulting in a measure of truly productive manufacturing time. It is calculated as:

OEE = [Performance] * [Quality] * [Availability]

Availability
Availability takes into account all events that stop planned production long enough where it makes sense to track a reason for being down (typically several minutes).

Availability is calculated as the ratio of Run Time to Planned Production Time:

Availability = DIVIDE([No Order],[Total Duration],0)

Run Time is simply Planned Production Time less Stop Time, where Stop Time is defined as all time where the manufacturing process was intended to be running but was not due to Unplanned Stops (e.g., Breakdowns) or Planned Stops (e.g., Changeovers).

Run Time = CALCULATE([Total Duration], FILTER(‘Fact’,‘Fact’[OEE Category] = “Run Time”))

Performance
Performance takes into account anything that causes the manufacturing process to run at less than the maximum possible speed when it is running (including both Slow Cycles and Small Stops).

Performance is calculated as:

Performance = DIVIDE([No Order Total], [Total Biscuit], 0 )

Quality
Quality takes into account manufactured parts that do not meet quality standards, including parts that need rework. Remember, OEE Quality is similar to First Pass Yield, in that it defines Good Parts as parts that successfully pass through the manufacturing process the first time without needing any rework.

Quality is calculated as:

Quality = DIVIDE([Good Biscuit],[Total Biscuit],0)

2 Likes

Hello Rachwen_Mesbehi,
Thank you very much for your answer. I still have some questions:
Are the specific values of 800 and 1800 that you are using as arguments to the RANDBETWEEN function for some reason?

The last question was related to the calculation of performance. If it is considered that there is only one machine working on several products, in the following example, for the day 07/01/2021 the Biscuits Filling Machine has 24 hours of gross time and has worked on two products, Jammy Creams and Custards Creams, If we know that for Jammy Creams it has stopped for 2 hours and for Custards Creams 1 hour, how do we know how much gross time corresponds to each product in those 24 hours? Biscuits Filling Machine and Jammy Creams Effective Time = Biscuits Filling Machine and Jammy Creams Gross Time - Biscuits Filling Machine and Jammy Creams Stop Time. I would like to know how you have considered this topic or someone from the forum.

Thanks a lot,

1 Like