Help With a Stacked Line Chart

[Will_Smothers]

We are a manufacturing company and we are working on ingesting data in PowerBI to help us drive efficiencies. Currently we are collecting the following data into an Excel spreadsheet:

• Date of production (not time just date)
• Machine the part was run on
• Customer the part was for
• The Work Order Number
• The Part Number
• The schedule time it should take to make the part
• The actual time it took to make the part
• Number of pieces completed
• Notes about the run

The issue is that all of the columns are going to have the same numbers multiple times and when I import the data into PowerBI, it wants to sum them and not show the actual times. My goal with the Stacked Line Chart is to have the Part number on the X-axis, the Y-axis be time and the two stacked lines be Scheduled time vs Actual time. A filter could be applied to the chart to help narrow down the scope based on the date. Here is some sample data:

DATE	MACHINE	CUSTOMER	W/O#	PART#	SCHED: RATE/PC	ACT: RATE/PC:
Monday, September 30, 2024	LT7	Customer1	W080565	109427	00:18.0	00:19.0
Monday, September 30, 2024	LT7	Customer1	W079103	118989	00:30.0	00:21.3
Monday, September 30, 2024	LT7	Customer1	W079107	129373	00:30.0	00:21.0
Tuesday, October 8, 2024	LT7	Customer2	W0285468	285468	00:24.0	00:00.0
Tuesday, October 8, 2024	LT7	Customer1	W0808484	285968	00:24.0	00:27.0
Wednesday, October 9, 2024	LT7	Customer1	W080603	288647	00:30.0	00:16.0
Wednesday, October 9, 2024	LT7	Customer1	W080602	288698	00:30.0	00:16.0
Tuesday, October 8, 2024	LT7	Customer3	W080564	414134	00:15.0	00:00.0
Monday, October 7, 2024	LT7	Customer3	W080551	414201	00:18.0	00:25.0
Monday, October 7, 2024	LT7	Customer3	W080551	414202	00:15.0	00:21.0
Tuesday, October 8, 2024	LT7	Customer3	W080564	414206	00:36.0	00:57.0
Tuesday, October 8, 2024	LT7	Customer3	W080564	414207	00:36.0	01:00.0
Tuesday, October 8, 2024	LT7	Customer3	W080564	414208	00:12.0	00:25.0
Monday, September 30, 2024	LT7	Customer3	W080356	414380	01:00.0	00:15.0

Any help anyone can provide me on this would be GREATLY appreciated. I have been struggling with how to make this work for two weeks. Once I have it in PowerBI, I want to publish it in Teams (which is where the Excel file lives) so that we have graphical representation of the our efficiency.

Thanks in advance for this communities help with this.

[Will_Smothers]

THANK YOU!!! That is exactly what I needed!

What this post has shown me is that I need to really dig into writing DAX and start understanding it better! I struggled with this for two solid weeks and you fixed it with four lines of DAX!!

Thank you very Much Miguel!!!

[MFelix]

Hi @Will_Smothers ,

The main question over here is the fact that visualizations don't play nice with time or date formats, so you need to transform them into numbers, in this case decimals, and them make a formatting of that result.

[MFelix · Accepted solution]

Hi @Will_Smothers ,

Create the following two measures:

Schedulle = HOUR(MAX('Table'[SCHED: RATE/PC]))+MINUTE(MAX('Table'[SCHED: RATE/PC]))/100

Actual = HOUR(MAX('Table'[ACT: RATE/PC:]))+MINUTE(MAX('Table'[ACT: RATE/PC:]))/100

Add the dynamic format in the following way:

"""" & FORMAT(MAX('Table'[ACT: RATE/PC:]), "hh:mm") & """"

"""" & FORMAT(MAX('Table'[SCHED: RATE/PC]), "hh:mm:ss") & """"

Now add it to the visual:

Check PBIX file attach.